I wrote this snippet of VBA code:
With src.Sheets(1).Range("A1").CurrentRegion
Cells.Find(What:="Weather", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, _
SearchFormat:=False).Activate
On Error Resume Next
If myList(w) = "Weather" And IsEmpty(ActiveCell.Offset(0, 1).Value) Then
w = "No"
ElseIf myList(w) = "Weather" And Not IsEmpty(ActiveCell.Offset(0, 1).Value) Then
w = "Yes"
MsgBox (MyFile & "+" & ActiveCell.Address & "_ " & ActiveCell.Offset(0, 1).Value & " +" & "Weather")
Else
word = ActiveCell.Offset(0, 1).Value
End If
Aim: is to find the value "Weather" in the CurrentRegion around "A1", and if cell offset(0,1) to the "weather" cell address is empty, variable w is "No"; otherwise, variable w is "yes", and pop message box as described in the code.
Issue: Code is finding value "Weather" in cell $H$13 (active cell with green frame) where apparently there is no such value, nor even hidden. (please see screenshot below with message box with: activecell address ($H$13), offset cell value (" ") and value to be found ("Weather"))error.png
Question: Why is the function Find working like this? And how to correct that?
Thank you very much in advance.
Tiago
Bookmarks