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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,906
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,397
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,112,817
Messages
5,542,670
Members
410,567
Latest member
SCraig123
Top