View Full Version : [SOLVED] Lookup Value in Range for an If Statement

05-28-2014, 10:52 AM
Hello Experts,

I have a situation where I have Variable X. I want to look to see if that variable is in a Range on another Sheet ("Summary"). I am trying to use it with an If statement. I tried something like this, and it doesn't seem to work. I am sure I am missing something small. I dont need to return the value, just run the IF statement if its found, if not, end if.

If Sheets("Summary").Columns("B17:B" & Cells(Rows.Count, "C").End(xlUp).Row).Find(What:=X, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False) Then


05-28-2014, 11:35 AM
I was able to use the Match function to get it to work, here is the code I used.

CC = ActiveSheet.Cells(RowCnt, ColCnt - 2).Value
IsCC = Application.Match(CC, Worksheets("Summary").Range("B16:B" & Cells(Rows.Count, "B").End(xlUp).Row), 0)
On Error Resume Next
If IsCC <> 0 Then

05-28-2014, 03:46 PM
Haven't tried the code but Find is usually preferable in VBA. Were you looking for a date?