?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]

Hi. Quick reply, so I don't get sacked due to inadequate sleep.

Hope I didn't sound smug when I said "same as my signature". I detest smugness in myself or others. Causes needless irritation! I suppose that the emoticons are supposed to convey reassurance that about the tone of a comment, but a grin after a smart comment can just seem passive-aggressive. Anyways...

I'm afraid I don't know much on the evolution of Excel or the introduction of the CellFormat Object, but you may well be right.

Re Overall Aim:
Basically I am highlighting a range of cells related to the active cell, but have to remove any old highlighting before applying current highlighting. Although there should be only one old range to "UnHighlight" I'm checking every cell once, to be sure. Speed is important though as some user may "scroll" using the arrow keys (I'm not referring to ScrollLock here btw). So short answer is that No, I am not doing another operation besides changing formatting I'm just changing the interior colour from light yellow or "Highlighted" to tan or "UnHighlighted". Not changing cell content or doing other operations as a result of the Find operation.

Incidentally, this code is in the WorkSheet_SelectionChange Event Sub.

Goodnight!

 

[AlphaFrog]

A couple of comments.

I see now why you are checking if the .Find within the Do-Loop returns nothing. You are changing the Format of each found cell with your "Do stuff" code and ultimately there will be no more cells to .Find. You actually will never reach back to the first found cell since it wouldn't be found again if you previously changed its format when it was first found.

On setting rngCellToRemoveOldHighlight = Nothing
When you declare (Dim) a variable within a procedure, those variable are destroyed when the procedure ends unless you use the Static keyword. In your code, it looks like it's superfluous to Set rngCellToRemoveOldHighlight = Nothing at the end, but it doesn't hurt.

.Find vs. .Findnext
My guess is that they would be the same speed within your Do-Loop. I would assume that .Findnext actually uses the same underlying code as .Find

I would suggest the following code structure. The code doesn't change the format of first-found-cell until it is found a second time. Finding the first-found-cell the second time ends the loop and negates the need to check within the Do-Loop for an Is Nothing situation.

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  [I]' Find next before changing the first found cell. The code will return to this cell again and then end Do-Loop[/I]

            Set rngCellToRemoveOldHighlight = rngCellsToCheckForOldHighlight.FindNext(rngCellToRemoveOldHighlight)

            With rngCellToRemoveOldHighlight
                
            Debug.Print "Cells Found At: " & .Address
                
[COLOR="Red"]'**********************************
'DO STUFF
'**********************************[/COLOR]
                
            End With
                

        Loop While rngCellToRemoveOldHighlight.Address <> strFirstAddress

    Else

        Debug.Print "Cells Not Found"

    End If

 

[Default300]

Hi AlphaFrog and GTO (Mark).

I haven't been able to work on this project for a few days, nor to log in and respond.

I just have a couple of minutes now, so I'll be brief.

AlphaFrog, thanks for those comments and insights. The first explains why I never saw that debug.print statement (see post #7).

I haven't tried your code structure yet, but will.

I really appreciate the contributions from both of you. Hope I can return the favour some day, or pass on the karma to others.