Match entire cell contents using VBA

[Ragavhere]

Hi,

i get the error "Object variable or with block variable not set" with the below code.

sub findcolumn()
Dim FindWeek as string
FindWeek = "Wk1"
Range("A2:BL2").Select
Selection.find(What:=FindWeek, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
nCurrentWeekCol = ActiveCell.Column
Endsub

when i change LookAt:=xlWhole as LookAt:=xlPart i dont get the error.

 

[Trebor76]

It's because there no entry in the range A2:BL2 that only says "Wk1", but it is part of a text string i.e. "Wk1 Sales"

 

[AlphaFrog]

Or you may have a cell with Wk1 with a trailing space after. It would "look" like Wk1 but would not match with xlWhole because of the trailing space.

 

[Ragavhere]

How do i modify the code such that the entire cell content is matched?

 

[Trebor76]

How do i modify the code such that the entire cell content is matched

Change the 'FindWeek' variable to reflect the entire string (cell content) you're searching for, not just 'Wk1' i.e.

FindWeek = "Wk1 Sales"

in my example.