?Error 91 on: Loop While Not Range Is Nothing And Range.Address <> String

[Default300]

Full Code can be posted if required.

It is derived from an apparently functional Find / Find Next Do / Loop While routine that I found online.

This is the close of the Loop. The Loop should be exited if either there are no more cells matching search criteria (first condition); or if the range found matches the first range found (second condition), indicating that the Find operation has looped back to start.

Problem
I get:
Run-time error '91':
Object variable or With block variable not set

On the following line of code:

[FONT=Courier New]Loop While Not rngRange Is Nothing _
    And rngRange.Address <> strRangeString[/FONT]

When: rngRange Is Nothing.

Troubleshooting
I presume the problem is that the second condition (And rngRange.Address <> strRangeString), can't be resolved since rngRange Is Nothing.

Is there a better way to code this?

Proposed Solution (1)
Would the following code work? Or does first condition need the double negative: Not Range Is Nothing?

NB: As well as changing And to Or; While to Until; and Negative conditions to Positive; I'm also proposing to store first range found as a Range, not an address String:

[FONT=Courier New]Loop Until rngRange Is Nothing _
    Or rngRange = rngRangeFirstFound[/FONT]

Proposed Solution (2)
Alternatively, should I check for the conditions, using 2 If Statements, as follows:

[FONT=Courier New]If [/FONT][FONT=Courier New]Not rngRange Is Nothing Then[/FONT]

[FONT=Courier New]    If [/FONT][FONT=Courier New]rngRange.Address <> [/FONT][FONT=Courier New]strRangeString[/FONT][FONT=Courier New] Then

        Exit Do
[/FONT][FONT=Courier New]
Loop While Not rngRange Is Nothing[/FONT]

Or some combination of the three? Thanks.

 

[Default300]

Sorry, editting time exceeded, so couldn't correct a couple of mistakes in Proposed Solution (2).

It should Read "=", not "<>", and have "End If"s

[FONT=Courier New]If Not rngRange Is Nothing Then

    If rngRange.Address = strRangeString Then

        Exit Do

    End If

[/FONT][FONT=Courier New]End If[/FONT]
[FONT=Courier New]
Loop While Not rngRange Is Nothing[/FONT]

 

[Norie]

If nothing is found then the code should never even reach that line.

That's kind of the point of the If statement - nothing found, do nothing and move on.

What is the code actually meant to do?

 

[Default300]

Thanks for your response.

What is the code actually meant to do?

It is a Find / Find Next Routine.
A small, Single Area Range is searched for Cells with a given Format.
If any cell matches the Search Criteria, it is reformatted, then the next cell is examined.

If nothing is found then the code should never even reach that line.

That's kind of the point of the If statement - nothing found, do nothing and move on.

I'm not sure why you said this. I don't see where I expresssed any doubt about this. Perhaps you composed your post before I was able to post my edit of the original post?

 

[AlphaFrog]

You want to do an initial Find
Then test if Not range is Nothing
Then a Do-Loop with a FindNext

Example:

Set rngFound = Activesheet.Usedrange.Find("Find something", ...)

If Not rngFound is Nothing then
   [I]' You found something. Findnext will never return nothing[/I]
   strFoundfirst = rngFound.Address
   Do
       [I]'code to do something with rngFound goes here[/I]

       [I]'Find the next one[/I]
       Set rngFound = Findnext(rngFound)
   Loop While rngFound.Address <> strFoundfirst
Else
   Msgbox "Couldn't find anything"
End if

 

[GTO]

Thanks for your response.

It is a Find / Find Next Routine.
A small, Single Area Range is searched for Cells with a given Format.
If any cell matches the Search Criteria, it is reformatted, then the next cell is examined...

Greetings,

I think that I'm on the same page as Norie, in that, there is normally an IF check before entering the Do...Loop While, to check if the value was found at least once. That said, your questions seem very sensible, but I think we do need to see the code inclusive of the initial IF and the loop.

For the moment, let's use the example from Help, modified a bit:

Option Explicit
    
Sub exa()
Dim c As Range
Dim firstAddress As String
    
    With Worksheets(1).Range("a1:a500")
        Set c = .Find(2, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Interior.Pattern = xlPatternGray50
                Set c = .FindNext(c)
                
                If MsgBox("Current found cell is: " & c.Address(False, False) & vbCrLf & _
                          "Exit now?", vbYesNo + vbDefaultButton2, vbNullString) = vbYes Then
                    
                    Exit Do
                End If
                
            Loop While Not c Is Nothing 'And c.Address <> firstAddress
        End If
    End With
    
End Sub

Presuming the range 'c' is initially set successfully (ie - there's at least one '2' in the range), then 'c' will never become Nothing, as it will just keep looping. So I think you have a good point in questioning the help example's logic.

That said, how are you managing to get the range to become Nothing?

Mark

 

[Default300]

Hi Mark (GTO) and AlphaFrog.

Thanks for your input, and your helpful tone!

Perhaps I should have posted the full code up front. Tricky to get the balance though, between providing sufficient info for people to understand the issue, and keeping it succinct, not verbose.

I've used the real variable names, rather than the more generic ones I used initially.

Option Explicit

    Dim rngCellsToCheckForOldHighlight As Range
    Dim rngCellToRemoveOldHighlight As Range
    Dim strFirstAddress As String
    
    
    With Application.FindFormat

        .Interior.ColorIndex = 36       '(light yellow)

    End With


    Set rngCellToRemoveOldHighlight = rngCellsToCheckForOldHighlight.Find( _
        What:="", After:=rngCellsToCheckForOldHighlight.Cells(1), _
        LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True)


    If Not rngCellToRemoveOldHighlight Is Nothing Then
    
        strFirstAddress = rngCellToRemoveOldHighlight.Address

        Do

            With rngCellToRemoveOldHighlight
                
            Debug.Print "Cells Found At: " & .Address
                
[COLOR=Red]'**********************************
'DO STUFF
'**********************************[/COLOR]
                
            End With
                
[COLOR=Red]'**********************************
'NEXT LINE IS VESTIGIAL CODE, left over from development process, but uses FindNext as AlphaFrog suggested.
'Can't remember why I abandoned it.
'?May have been due to my search being for a format (not number, text formula etc).
'?May have been due cells in my range being annoying merged cells.[/COLOR]

            ''Set rngCellToRemoveOldHighlight = rngCellsToCheckForOldHighlight.FindNext(rngCellToRemoveOldHighlight)
[COLOR=Red]'**********************************[/COLOR]
            
            Set rngCellToRemoveOldHighlight = rngCellsToCheckForOldHighlight.Find( _
                What:="", After:=rngCellToRemoveOldHighlight, _
                LookIn:=xlFormulas, LookAt:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=True)

[COLOR=Red]'**********************************
'ERROR HERE...[/COLOR]

        ''Loop While Not rngCellToRemoveOldHighlight Is Nothing _
            And rngCellToRemoveOldHighlight.Address <> strFirstAddress

[COLOR=Red]'CHANGED TO BLOCK BELOW...
'**********************************[/COLOR]

            If Not rngCellToRemoveOldHighlight Is Nothing Then
            
                If rngCellToRemoveOldHighlight.Address = strFirstAddress Then
            
                    Debug.Print "Same Address As First Address - Exiting Loop"
                    
                    Exit Do
            
                End If
            
            End If
            
        Loop While Not rngCellToRemoveOldHighlight Is Nothing
        
    Else

        Debug.Print "Cells Not Found"

    End If

    Set rngCellToRemoveOldHighlight Is Nothing

I have just tested the revised code block at the bottom and it seems to work. (Not crashing! Replaces all found instances rapidly.) [EDIT: This Debug.Print statement has not yet appeared in the Immediate Window though: "Same Address As First Address - Exiting Loop", so that condition does not appear to be arising, even if I the top left cell in the range is a positive match for the Find operation]

I may try the FindNext approach again, if you think it may be even more efficient. (Currently takes 0.15 seconds if All Cells in Search Range need to be reformatted.) As I mention in the code though, I thought there was a reason for abandoning it!
Perhaps that I'm only concerned with the Format of the Cell - the Content is irrelevant.
Or perhaps because each Cell is a merged block (7 Cells wide x 1 Cell high)

That is an interesting point that Mark makes though...

... how are you managing to get the range to become Nothing?

I only deliberately set it to Nothing after the Loop is complete. Actually, thinking about it, I suppose it becomes Nothing when it is Set to the result of a Find operation that returns Nothing.

 

[GTO]

I am afraid that I'm off to the sack for a couple of hours, but will try and check back later.

Currently I'm in Excel 2000, and do not find any Application.FindFormat. What version of Excel are you using?

As I do not know what .FindFormat does exactly, I would presently be making wild and most likely, bad guesses.

I did spot this at the end: 'Set rngCellToRemoveOldHighlight Is Nothing'

That should be: 'Set rngCellToRemoveOldHighlight = Nothing"

Mark

 

[Default300]

I did spot this at the end: 'Set rngCellToRemoveOldHighlight Is Nothing'

That should be: 'Set rngCellToRemoveOldHighlight = Nothing"

True. Thanks. Unfortunately, I didn't write that line of code in the VBE, where the syntax checker would have alerted me to the error, I typed it directly into my post. Cheers though.

Re: Excel Version(s). Same as my signature, 2002, and 2003, with VBA6.5.

Re: FindFormat (from 2003 Help)...

Sets or returns the search criteria for the type of cell formats to find.

expression.FindFormat

expression Required. An expression that returns one of the objects in the Applies To list. (*the Applies To list contains one object: Application Object)

These criteria will be included in a Find operation if the Find Parameter, SearchFormat:=True. If False they are ignored. I think they are retained from one Find operation to another unless cleared, but I may be mistaken. They are they equivalent of clicking Options / Format in the Find Dialogue of the User Interface.

I also need to get some "shut-eye"! It's 03:06. Up for work again in 4 hours. Probably won't check back till this evening. Thanks to those who are helping.

 

[GTO]

Re: Excel Version(s). Same as my signature, 2002, and 2003, with VBA6.5.
...I also need to get some "shut-eye"! It's 03:06. Up for work again in 4 hours. Probably won't check back till this evening. Thanks to those who are helping.

Oh dear! Cheers back of course.

I take it as you mention having XL2002, that the CellFormat Object became available in that version?

I had found online ms help for 2003, so hopefully gained 1/2 a clue.

Anyways, I presume I am safe in assuming that you are not simply replacing formatting, but that 'Do Stuff' is other operations. Hopefully I am not mis-reading, but it seems to me that you are looking to speed up or find the best way to loop through a range, using cells' formatting as the LookFor so-to-speak, and perform some operation(s)?

Might you put together a small example where we can easily replicate rngCellsToCheckForOldHighlight and what the 'Stuff' is to be done. At least for me, so far, it would seem that the example in Help would be close.

Thanks,

Mark