Excel VBA Activate method of Range class failed

user_7821579 0
Viewed 1665

I have a code that I used in a different macro and wotked that now is not working and I keep getting run time error 1004 in the line with activate:

Sub initialize()

dim MonthName  as string
dim MainWB  as workbook
dim VisualWS as worksheet

MonthName = cells (1,1).value
Set MainWB = ThisWorkbook
Set VisualWS = MainWB.Worksheets("Visual")

With VisualWS
    .Range("L1:W1").Find(MonthName, , xlValues, xlWhole).Activate
End With

MonthCol = ActiveCell.Column

End Sub
vba excel
2 Answers

According to the docs on Range.Find, the "method returns Nothing if no match is found."

Most likely, your .Range().Find() method isn't finding any matches and therefore returns Nothing, which doesn't have a function Activate.

Try this instead:

Dim findResult As Range
Set findResult = .Range("L1:W1").Find(MonthName, , xlValues, xlWhole)
If findResult <> Nothing then findResult.Activate
user_88745450

Find might well return Nothing, so I would rather write:

With VisualWS
    set target = .Range("L1:W1").Find(MonthName, , xlValues, xlWhole)
End With
If target is Nothing Then 
    DoSomething
else
    MonthCol = target.Cells(1,1).Column
End if
user_785220
Related