Find and FindNext...from Excel VBA Help generates 91 error.
 

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

Find and FindNext...from Excel VBA Help generates 91 error.
 

On Tue, 31 Mar 2009 11:46:59 -0700 (PDT), Matt
wrote:


Oddly the code generated an error message 91 "Object variable or with
block variable not set".

Loop While Not c Is Nothing And c.Address < firstAddress

On this line? I get that too. If c Is Nothing, the c.Address give an
error. This line needs to be two lines

If c.Address = firstAddress Then Exit Do
Loop While Not c Is Nothing


--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Find and FindNext...from Excel VBA Help generates 91 error.
 

Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.



Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--

Dave Peterson

Find and FindNext...from Excel VBA Help generates 91 error.
 

On Mar 31, 4:10*pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.





Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(2, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Value = 5
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

Find and FindNext...from Excel VBA Help generates 91 error.
 

Those minor tweaks with inadequate testing are too easy to make--even for MS.

Matt wrote:

On Mar 31, 4:10 pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.





Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

--

Dave Peterson

Find and FindNext...from Excel VBA Help generates 91 error.
 

On Mar 31, 4:40*pm, Dave Peterson wrote:
Those minor tweaks with inadequate testing are too easy to make--even for MS.





Matt wrote:

On Mar 31, 4:10 pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.

Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(2, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Value = 5
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Feel like kind of a jerk but I finally ran the code...and oddly I
still get the "91" error message.
The code makes sense but the VBE doesn't like it..Other suggestions?

Find and FindNext...from Excel VBA Help generates 91 error.
 

On Mar 31, 4:51*pm, Matt wrote:
On Mar 31, 4:40*pm, Dave Peterson wrote:





Those minor tweaks with inadequate testing are too easy to make--even for MS.

Matt wrote:

On Mar 31, 4:10 pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.

Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(2, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Value = 5
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Feel like kind of a jerk but I finally ran the code...and oddly I
still get the "91" error message.
The code makes sense but the VBE doesn't like it..Other suggestions?- Hide quoted text -

- Show quoted text -


Not as pretty but the code below actually worked...I had to check if
there was
such a range before asking if it was the first address (thought the
loop would have
handled that reference issue but maybe because it's a do so it reads
first and loops last).

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(5, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 2

Set c = .FindNext(c)
If Not c Is Nothing Then
If c.Address = firstAddress Then Exit Do
End If

Loop While Not c Is Nothing
End If
End With
End Sub

Find and FindNext...from Excel VBA Help generates 91 error.
 

I get easily confused by the different flavors of do/loop, do/while, do/until.
I'll just check myself.

And it's a very good idea to declare your variables. You'll get the helpful
intellisense to popup and by using "option explicit", if you make a typo, your
code may not even compile.

And even though the sample code didn't do it, it's a good idea to specify all
the parms to that .find command. If you don't specify them, then you'll be
inheriting the last settings used -- either by code or by the user. And that
kind of bug can be very difficult to find.

Option Explicit
Sub another_find2()
Dim FoundCell As Range
Dim FirstAddress As String

With Worksheets(1).Range("a1:a500")
Set FoundCell = .Cells.Find(What:=2, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "Not found"
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Value = 2

Set FoundCell = .FindNext(FoundCell)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If

Loop
End If
End With

End Sub



Matt wrote:

On Mar 31, 4:51 pm, Matt wrote:
On Mar 31, 4:40 pm, Dave Peterson wrote:





Those minor tweaks with inadequate testing are too easy to make--even for MS.

Matt wrote:

On Mar 31, 4:10 pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.

Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Feel like kind of a jerk but I finally ran the code...and oddly I
still get the "91" error message.
The code makes sense but the VBE doesn't like it..Other suggestions?- Hide quoted text -

- Show quoted text -

Not as pretty but the code below actually worked...I had to check if
there was
such a range before asking if it was the first address (thought the
loop would have
handled that reference issue but maybe because it's a do so it reads
first and loops last).

Sub another_find()
With Worksheets(1).Range("a1:a500")
Set c = .Find(5, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 2

Set c = .FindNext(c)
If Not c Is Nothing Then
If c.Address = firstAddress Then Exit Do
End If

Loop While Not c Is Nothing
End If
End With
End Sub

--

Dave Peterson

Find and FindNext...from Excel VBA Help generates 91 error.
 

On Mar 31, 6:08*pm, Dave Peterson wrote:
I get easily confused by the different flavors of do/loop, do/while, do/until.
I'll just check myself.

And it's a very good idea to declare your variables. *You'll get the helpful
intellisense to popup and by using "option explicit", if you make a typo, your
code may not even compile.

And even though the sample code didn't do it, it's a good idea to specify all
the parms to that .find command. *If you don't specify them, then you'll be
inheriting the last settings used -- either by code or by the user. *And that
kind of bug can be very difficult to find.

Option Explicit
Sub another_find2()
* * Dim FoundCell As Range
* * Dim FirstAddress As String

* * With Worksheets(1).Range("a1:a500")
* * * * Set FoundCell = .Cells.Find(What:=2, _
* * * * * * * * * * * * * * After:=.Cells(.Cells.Count), _
* * * * * * * * * * * * * * LookIn:=xlValues, _
* * * * * * * * * * * * * * LookAt:=xlWhole, _
* * * * * * * * * * * * * * SearchOrder:=xlByRows, _
* * * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * * MatchCase:=False)

* * * * If FoundCell Is Nothing Then
* * * * * * MsgBox "Not found"
* * * * Else
* * * * * * FirstAddress = FoundCell.Address
* * * * * * Do
* * * * * * * * FoundCell.Value = 2

* * * * * * * * Set FoundCell = .FindNext(FoundCell)

* * * * * * * * If FoundCell Is Nothing Then
* * * * * * * * * * Exit Do
* * * * * * * * End If

* * * * * * * * If FoundCell.Address = FirstAddress Then
* * * * * * * * * * Exit Do
* * * * * * * * End If

* * * * * * Loop
* * * * End If
* * End With

End Sub





Matt wrote:

On Mar 31, 4:51 pm, Matt wrote:
On Mar 31, 4:40 pm, Dave Peterson wrote:

Those minor tweaks with inadequate testing are too easy to make--even for MS.

Matt wrote:

On Mar 31, 4:10 pm, Dave Peterson wrote:
Dick gave you the solution, but the problem occurred because MS changed this
example.

In earlier versions, instead of changing the value, the code changed the fill
color (or font or some kind of format).

That meant that the code would still find the value (since it hadn't changed)
and the code wouldn't break.

Matt wrote:

Hello,

I've always had a hard time understanding the find and findnext
methods so to test I copied the code presented in the help file to my
worksheet and ran the code.
Oddly the code generated an error message 91 "Object variable or with
block variable not set". I've never had a situation where the VBA Help
code creates an error message..so I'm kind of lost at figuring it out
Any guidance would be appreciated. Thanks, Matt

Here's the code:

Sub another_find()
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(2, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Value = 5
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks to you both for the code and the explanation. It was driving me
nuts and I refused to put some kind of error handler in there...

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Feel like kind of a jerk but I finally ran the code...and oddly I
still get the "91" error message.
The code makes sense but the VBE doesn't like it..Other suggestions?- Hide quoted text -

- Show quoted text -

Not as pretty but the code below actually worked...I had to check if
there was
such a range before asking if it was the first address (thought the
loop would have
handled that reference issue but maybe because it's a do so it reads
first and loops last).

Sub another_find()
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(5, LookIn:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Value = 2

* * * * * * Set c = .FindNext(c)
* * * * * * If Not c Is Nothing Then
* * * * * * If c.Address = firstAddress Then Exit Do
* * * * * * End If

Loop While Not c Is Nothing
* * * * End If
End With
End Sub

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks again...I studied your code a bit and it definately seems more
complete...
memorizing the .find params and understanding them seems worth the
effort
One of the things I noticed is that you use the After:=.Cells
(.Cells.Count)
This pushes the .find to start at cell 1 instead of cell 2 which based
on the help file seems to be
the default.

Find and FindNext...from Excel VBA Help generates 91 error.
 

I either steal that .find code from another location or I record a small macro
when I do it manually (or just use VBA's help if I'm really industrious).

And I like to specify the after:= parm, too. Sometimes, I'll use:
...., after:=cells(1), SearchDirection:=xlPrevious, ...
to find the last one in the range.



Matt wrote:

<<snipped

Thanks again...I studied your code a bit and it definately seems more
complete...
memorizing the .find params and understanding them seems worth the
effort
One of the things I noticed is that you use the After:=.Cells
(.Cells.Count)
This pushes the .find to start at cell 1 instead of cell 2 which based
on the help file seems to be
the default.

--

Dave Peterson

Find and FindNext...from Excel VBA Help generates 91 error.
 

Right...

Off topic: Just reviewed the Peterson sort using shapes to create
clickable column headers..nice.
(I've never use the application.caller)

Sort of On Topic: I find that printing off code and staring at it for
a whiles helps me grasp it alot better.

Dave Peterson wrote:
I either steal that .find code from another location or I record a small macro
when I do it manually (or just use VBA's help if I'm really industrious).

And I like to specify the after:= parm, too. Sometimes, I'll use:
..., after:=cells(1), SearchDirection:=xlPrevious, ...
to find the last one in the range.



Matt wrote:

<<snipped

Thanks again...I studied your code a bit and it definately seems more
complete...
memorizing the .find params and understanding them seems worth the
effort
One of the things I noticed is that you use the After:=.Cells
(.Cells.Count)
This pushes the .find to start at cell 1 instead of cell 2 which based
on the help file seems to be
the default.

--

Dave Peterson

Find and FindNext...from Excel VBA Help generates 91 error.
 

I've printed some sample code that I liked, too. But after a little bit, I ran
out of paper <bg.

You may want to save your ink and invest in a book (or two).

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Matt wrote:

Right...

Off topic: Just reviewed the Peterson sort using shapes to create
clickable column headers..nice.
(I've never use the application.caller)

Sort of On Topic: I find that printing off code and staring at it for
a whiles helps me grasp it alot better.

Dave Peterson wrote:
I either steal that .find code from another location or I record a small macro
when I do it manually (or just use VBA's help if I'm really industrious).

And I like to specify the after:= parm, too. Sometimes, I'll use:
..., after:=cells(1), SearchDirection:=xlPrevious, ...
to find the last one in the range.



Matt wrote:

<<snipped

Thanks again...I studied your code a bit and it definately seems more
complete...
memorizing the .find params and understanding them seems worth the
effort
One of the things I noticed is that you use the After:=.Cells
(.Cells.Count)
This pushes the .find to start at cell 1 instead of cell 2 which based
on the help file seems to be
the default.

--

Dave Peterson

--

Dave Peterson