This is not tested as no sample workbook to try it out on but off the top of my head something like this should work.
Dim blMatchFound As Boolean: blMatchFound = False
lnglastrow = Cells(Rows.Count, "A").End(xlUp).Row
intValueToFind = Sheets("Plan on a Page").Range("C4")
For i = 1 To lnglastrow
If Cells(i, 1).Value = intValueToFind Then
blMatchFound = True
Exit For
End If
Next i
If blMatchFound = True Then
'DO STUFF HERE
Else
MsgBox "No match.", , ""
Exit Sub
End If
Note, if you're only interested in if the reference is there or not rather than which row it's found in (or not) then you could just use FIND rather than looping through row by row.
It would be a lot quicker if you have LOTS of data.
Something like:
Dim r As Range
lnglastrow = Cells(Rows.Count, "A").End(xlUp).Row
intvaluetofind = Sheets("Plan on a Page").Range("C4")
Set r = Range("A1:A" & lnglastrow).Find(intvaluetofind, , xlValues, xlWhole)
If Not r Is Nothing Then
'DO STUFF HERE
Else
MsgBox "No match.", , ""
End If
BSB
Bookmarks