Search Attendance Sheet to see if people who registered attended (by their email) and set a cell to yes or no value

[mminten75]

I am looking for a way to search from one cell in my registration sheet and see if the string in that cell appears in column in an attendance sheet (to see if they showed up). If so I would like to just have the word Yes appear in Column T of the registration sheet. I would like to do this in VBA as part of my reporting software.

I currently use this formula in Column T of the registration sheet: =IF(ISNA(VLOOKUP(W2,'Attendance'!D:D,1,FALSE)),"no", "yes")
This will search for the email address in W2 in Column D of the attendance worksheet. If it exists it will place a "yes" in the cell where this formula appears.

How can I do this in VBA using a For Next command for each row?

 

[CountTepes]

You will have to tweak it for your purposes, but this should work if you put it in the code area of whatever worksheet, you want the answers

Sub FindAttendees()
For R = 2 To WhateverRowYouWantToEndAt
    If (IsError(WorksheetFunction.VLookup(Range(Cells(R, 23), Worksheets("Attendance").Range("D:D"), 1, False)))) Then
        Cells(R, WhateverColumnYouWant) = "No"
        Else
        Cells(R, WhateverColumnYouWant) = "Yes"
    End If
Next
End Sub