Issues with Error 91: Object variable or With block variable not set

[Truiz]

Good Morning,

I have the below code

Sub firstandlast()



Dim FCol As Integer
Dim lCol As Integer


FCol = ActiveSheet.Rows(1).Find(What = "Store Number (Location)", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Column
lCol = ActiveSheet.Rows(1).Find(What = "Store Number", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Column


MsgBox FirstCol & " And " & LastCol


End Sub

This should basically return the column number of the first instance of Store Number (Location) and the column number of the last instance of Store Number

But I'm getting Error 91 not sure why. Please advise

Thanks

 

[Fluff]

You're missing a colon after What, it should be

What:=

 

[Jonmo1]

Change
What = "Store Number...
to
What:= "Store Number...

 

[Marcelo Branco]

Try - corrections in red

Sub firstandlast()
Dim FCol As Integer
Dim lCol As Integer

FCol = ActiveSheet.Rows(1).Find(What[COLOR=#ff0000]:=[/COLOR]"Store Number (Location)", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Column
lCol = ActiveSheet.Rows(1).Find(What[COLOR=#ff0000]:=[/COLOR]"Store Number", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Column

MsgBox [COLOR=#ff0000]FCol[/COLOR] & " And " & [COLOR=#ff0000]lCol[/COLOR]
End Sub

M.

 

[Truiz]

Thanks... that was so stupid on my behalf...

 

[Marcelo Branco]

Thanks... that was so stupid on my behalf...

You are welcome.
To avoid such errors use Option Explicit
See
http://www.cpearson.com/excel/declaringvariables.aspx

M.