Using Find Function with If Function

[MASONS4]

Hi,

I'm trying to write some code, to search cells A33 to A100 to see if they contain the word "Holiday". If it does, I then need it to select the cell with holiday in (and ideally the cell next to is as well). Holiday will only appear once in the list if it is there at all.

My code writing is poor at best, but I'm unable to merge a "Find" with an "If".

Please see the below copy of my attempt:

Test = Range("A32:A100").Select

Set Test = Cells.Find(What:="Holiday", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If Test Is Holiday Then

Test.Activate
ActiveCell.Select 'and the cell next to it (in column B)

End If

As I mentioned earlier, holiday will only appear once, and will not be in every search.

Many thanks for any helps.

Sean

 

[MrKowz]

Try:

Dim rng As Range
Set rng = Range("A32:A100").Find(What:="Holiday", LookIn:=xlValues)
If Not rng Is Nothing Then
    rng.Resize(1, 2).Select
End If

 

[MASONS4]

That's done it.

Thanks a lot for your help.

Cheers,

Sean