VBA to search columns to find a number of variables

[daithiboy]

Hi folks,

I'm back again for more genius advice on how would I achieve the following...

I would like to run a macro that will search for a number of variables and assign them for later calculation. For instance, as simplified version:

I have a sheet with a number of pivot tables on it. I would like the macro to search Column "A" for the term "Total Attendance" and assign the integer value that is one column to the right as a variable ("TotAtt"). Then search column "D" for "New" and, as before, assign the value to the right as a variable ("New").

The hope would be that I could then use the macro to do some basic arithmetic and toss the results out to certain cells on an attendance worksheet e.g. if the variables above have been defined and assigned then I could do the following, this code is a complete roughwork and is probably completey wrong:

Dim Revisits, TotAtt, New as integer
TotAtt = Search column A for "Total Attendance" and offset one cell to the right
New = Search Column D for "New" and offset one cell to the right
Revisits = TotAtt - New

sheets("Attendance").range("A5").value = Revisits
Sheets("Attendance").Range("B5").value = TotAtt
Sheets("Attendance").Range("C5".value = New

Any thoughts? Does that make any sense at all?

Many thanks in advance,
Dave

 

[Fluff]

Maybe something like

Dim TotAtt As Range, myNew As Range
Dim Revisits As Long
Set TotAtt = Range("A:A").find("Total Attendance", , , xlWhole, , , False, False).Offset(, 1)
Set myNew = Range("D:D").find("New", , , xlWhole, , , False, False).Offset(, 1)
If TotAtt Is Nothing Or myNew Is Nothing Then Exit Sub
Revisits = TotAtt.Value - myNew.Value

Sheets("Attendance").Range("A5").Value = Revisits
Sheets("Attendance").Range("B5").Value = TotAtt.Value
Sheets("Attendance").Range("C5").Value = myNew.Value

 

[daithiboy]

Thanks a million Fluff, I was able to figure it out using your prompts.

Thanks a mil.

Dave

 

[Fluff]

Glad you got it sorted & thanks for the feedback