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

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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:

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:

Code:
[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:

Code:
[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.
 
Last edited:

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
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

Code:
[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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 13, 2009
Messages
83
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
You want to do an initial Find
Then test if Not range is Nothing
Then a Do-Loop with a FindNext

Example:

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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:

Rich (BB code):
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

Board Regular
Joined
Jul 13, 2009
Messages
83
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.

Code:
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.
 
Last edited:

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Board Regular
Joined
Jul 13, 2009
Messages
83
I did spot this at the end: 'Set rngCellToRemoveOldHighlight Is Nothing'

That should be: 'Set rngCellToRemoveOldHighlight = Nothing"
:oops: 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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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.
:oops: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
 

Forum statistics

Threads
1,081,849
Messages
5,361,679
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top