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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Mar 31, 4:51 pm, Matt <Matthew_Som...@xxxxxxxxx> wrote:
On Mar 31, 4:40 pm, Dave Peterson <peter...@xxxxxxxxxxxxxxxx> 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 <peter...@xxxxxxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: Find and FindNext...from Excel VBA Help generates 91 error.
    ... methods so to test I copied the code presented in the help file to my ... Thanks, Matt ...     If Not c Is Nothing Then ... still get the "91" error message. ...
    (microsoft.public.excel.programming)
  • Re: Unable to bind a 2nd time if recv receives all bytes sent
    ... an "Address already in use" error message. ... Why is socket/bind/listen inside a loop? ... listening socket each time? ...
    (comp.unix.programmer)
  • Re: Incorrect error?
    ... However, I think the error message GNAT is reporting is wrong, and ... and the error message GNAT produces doesn't make ...    while not ok loop ...
    (comp.lang.ada)
  • Re: Find and FindNext...from Excel VBA Help generates 91 error.
    ... intellisense to popup and by using "option explicit", if you make a typo, your ...     Dim FoundCell As Range ... methods so to test I copied the code presented in the help file to my ... still get the "91" error message. ...
    (microsoft.public.excel.programming)
  • Re: Random reboots
    ... After doing a repair/re-install of XP, ...  (I'd like to see an error message both because it might be ... and also I might get the memory stick replaced under warranty). ... System Manufacturer     INTEL_ ...
    (microsoft.public.windowsxp.help_and_support)