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

Truiz

Active Member
Joined
Jul 14, 2014
Messages
332
Good Morning,

I have the below code

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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
You're missing a colon after What, it should be
Code:
What:=
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Change
What = "Store Number...
to
What:= "Store Number...
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Try - corrections in red

Code:
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

Active Member
Joined
Jul 14, 2014
Messages
332
Thanks... that was so stupid on my behalf...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,819
Members
409,839
Latest member
akashsadhu
Top