Excel VBA Help - "searching a table and returning specific information"

prompovitccd

Junior Member
Jul 1, 2013
5
0
0
Howdy:

I am brand new to the whole coding scene and am trying to learn VBA to complete an excel project. I was hoping someone could provide me some guidance on the best way to start coding and let me know of any tutorials that could provide a solid foundation for me.

Here is a description of the code i'm trying to write:

I need to write a program that searches a table and returns any values that are contained within the table in a "pop-up" or dialogue box". Additionally, the code needs to return the row and column heading that a particular cell corresponds to.​

The row headings are a list of names and the column headings are a list of dates.

I know that I need to use some sort of loop function to run through the table and an "if" function to identify whether the cell has a value or not. If the cell doesn't have a value I need it to skip to the next cell - essentially I only want to return the cells which do have a value, which are only a few in the whole table.​

I have already started writing a set of step by step instructions so I can break down what needs to be done and in which order, but because I am new to VBA i'm not sure how the syntax should be set up and what commands are best to use.

I am excited to learn so any help would be appreciated.

Thanks so much!
 

Tweak155

Lifer
Sep 23, 2003
11,448
262
126
Ok need to be clear here - you want to look through a TABLE or through a set of data on an excel sheet that looks like a table?

Technically, even with the latter, you can configure an area of a sheet as a table and query it.

Your useful functions:

MsgBox - This will be your popup box
Code:
MsgBox "Hello World"

Cells: A specific cell on a specific sheet
Code:
ThisWorkbook.Sheets("SheetNameHere").Cells(1, 1) 'Cell A1 on sheet SheetNameHere
ThisWorkbook.Sheets("SheetNameHere").Cells(1, "A") 'Cell A1 on sheet SheetNameHere

Range: A group of cells or single cell on a specific sheet
Also included in this example: With
With will allow you to reference the same object repeatedly using a "."
Code:
ThisWorkbook.Sheets("SheetNameHere").Range("A1") 'Cell A1 on sheet SheetNameHere
ThisWorkbook.Sheets("SheetNameHere").Range("A1:A2") 'Cells A1 and A2 on sheet SheetNameHere
With ThisWorkbook.Sheets("SheetNameHere")
   .Range(.Cells(1, 1), .Cells(2, "A")) 'Cells A1 and A2 on sheet SheetNameHere
End With

ThisWorkbook just means the workbook that contains the code (only important if the user can have many workbooks open, or you open workbooks in memory. Good coding practice is to always specify). Sheets and Worksheets are interchangeable and specifies the object collection of Sheets.
 

prompovitccd

Junior Member
Jul 1, 2013
5
0
0
Ok need to be clear here - you want to look through a TABLE or through a set of data on an excel sheet that looks like a table?

Sorry, yes its a range of data in my excel sheet that looks like a table.
 

prompovitccd

Junior Member
Jul 1, 2013
5
0
0
There is a real simple way to do your request. But before I go into that, is this for homework or for your job?

This is for my job, i'm in a summer internship and they told me to go learn VBA.
 
Last edited:

Tweak155

Lifer
Sep 23, 2003
11,448
262
126
Here is probably the easiest way to do what you ask, but this isn't the best way to learn it:

Code:
Dim tempCell As Range, whichSht As Worksheet, curStr As String

Set whichSht = ThisWorkbook.Worksheets("Sheet1")

For Each tempCell In whichSht.Range("A2:B4").SpecialCells(xlCellTypeConstants)
    curStr = "Row: " & tempCell.Row & vbNewLine
    curStr = curStr & "Column Heading: " & whichSht.Cells(1, tempCell.Column) & vbNewLine
    curStr = curStr & "Value: " & tempCell.Value
    MsgBox curStr
Next tempCell

You would update the Range("A2:B4") to the range that contains only the data, and in the Column Heading line, replace the 1 in Cells(1, tempCell.Column) with the row the headers are found on.

A more appropriate learning method would be the following:

Code:
Dim curRow As Long, curCol As Long, lastRow As Long, lastCol As Long, startCol As Long

Dim curStr As String

'Set curRow to your first row with data
curRow = 2
'Same for Column, A = 1, B = 2, etc
startCol = 1 'Start on column "A"

'Specify what determines the end of the table, or how it should check
'Here I specify the last row, but you can also have it look for a condition

lastRow = 15
lastCol = 4 'End on column "D"

'Loop through the rows
While curRow <= lastRow
    'Loop through the columns
    curCol = startCol 'Always reset this to start the search over
    While curCol <= lastCol
        With ThisWorkbook.Sheets("Sheet1")
            If .Cells(curRow, curCol).Value <> "" Then
                curStr = "Row: " & curRow & vbNewLine
                curStr = curStr & "Column Heading: " & .Cells(1, curCol) & vbNewLine 'Replace 1 with the header row
                curStr = curStr & "Value: " & .Cells(curRow, curCol).Value
                MsgBox curStr
            End If
        End With
    curCol = curCol + 1 'You MUST increment curCol or you will have an infinite loop
    Wend
curRow = curRow + 1 'You MUST increment curRow or you will have an infinite loop
Wend
 
Last edited:

prompovitccd

Junior Member
Jul 1, 2013
5
0
0
[QUOTEA more appropriate learning method would be the following:][/QUOTE]

Thanks so much for the code, as you suggested I am using the longer code so I can more closely follow along - Couple questions though:

In the "longer code" above, the code seems to be duplicated twice (repeats itself half way down), was that intentional? Also, I have adjusted the appropriate data inputs and the program seems to be running fine (no errors come up), but the "pop-up" box that I suppose to return the information isn't coming up. Any ideas?
 

Tweak155

Lifer
Sep 23, 2003
11,448
262
126
A more appropriate learning method would be the following:]

Thanks so much for the code, as you suggested I am using the longer code so I can more closely follow along - Couple questions though:

In the "longer code" above, the code seems to be duplicated twice (repeats itself half way down), was that intentional? Also, I have adjusted the appropriate data inputs and the program seems to be running fine (no errors come up), but the "pop-up" box that I suppose to return the information isn't coming up. Any ideas?

Try it now. I goofed when I was trying to fix a comment earlier.
 

prompovitccd

Junior Member
Jul 1, 2013
5
0
0
Try it now. I goofed when I was trying to fix a comment earlier.

Yep, that solved it thanks!

One more question for you though... each time a value is returned it is displayed in its own "pop-up" box- so say there are 10 values that get returned, it will come up in 10 unique pop-up box's

Is there anyway to get the program to return all of the values in one pop-up box, So its in a more organized and easily readable format?
 

Tweak155

Lifer
Sep 23, 2003
11,448
262
126
Yep, that solved it thanks!

One more question for you though... each time a value is returned it is displayed in its own "pop-up" box- so say there are 10 values that get returned, it will come up in 10 unique pop-up box's

Is there anyway to get the program to return all of the values in one pop-up box, So its in a more organized and easily readable format?

You can design the format any way you want, but you're pretty limited when using pop ups.

I captured the data for you, maybe you can play with it and get a look you like.