none
Excel 2010 FindNext Producing Error 91 RRS feed

  • Question

  • A newly created VBA Project is resulting in this error after the FindNext method.

    The error being 'Object variable or next With Block not set.

    Other than my program failing, Microsoft’s own example:

    Sub findtwo()

    With Worksheets(1).Range("a1:a31")

        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

    produces the same error

    As does the following simple trial program fail with the same error

    Sub FindIndustry()

        Dim varIndFound         As Variant

        Dim varFirstIndAddr     As Variant

        With ActiveSheet.Range("C3:C96")

            Set varIndFound = .Find("Non-Profit", LookIn:=xlValues, lookat:=xlWhole)

            Set varFirstIndAddr = varIndFound.Address

            If Not varIndFound Is Nothing Then

                Do

                    MsgBox varIndFound.Address

                    Set varIndFound = .FindNext(varIndFound)

                Loop While Not varIndFound Is Nothing And varIndFound.Address <> varFirstIndAddr

            End If

        End With

    End Sub

    Can anybody explain or at least offer a work around.

    Thanks in anticipation

    Tuesday, August 4, 2015 2:30 AM

Answers

  • Re:  using Find in VBA

    In the MS code...
    If the data is not found then then object c is nothing.
    If c is nothing then it has no address and the line...
        "Loop While Not c Is Nothing And c.Address <> firstAddress"
    is trying to compare c.address (which doesn't exist) to a String.
    Excel/vba just won't compare items that are not the same type.
    (must be numbers vs. numbers, text vs. text, etc.)
    '---

    Try replacing the above line with two lines...
               If c Is Nothing Then Exit Do
            Loop Until c.Address = firstAddress
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Tuesday, August 4, 2015 3:57 AM
  • Can anybody explain or at least offer a work around.

    IMHO you'll find the function below helpful. Here is a simple sample how to use it:

    Sub FindIndustry()
      Dim R As Range
      Set R = FindAll(Range("C3:C96"), "Non-Profit")
      If R Is Nothing Then
        MsgBox "Not found"
      Else
        MsgBox "Found at " & R.Address(0, 0)
      End If
    End Sub

    Andreas.

    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As Object 'Dictionary
      Dim Temp() As Variant
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      'Initialize our internal stack
    #If Mac Then
      Set Stack = New Dictionary
      Stack.CompareMode = vbTextCompare
    #Else
      Set Stack = CreateObject("Scripting.Dictionary")
    #End If
    
      FirstAddress = c.Address
      Do
        Stack.Add Stack.Count, c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      Temp = Stack.Items
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    Tuesday, August 4, 2015 10:08 AM

All replies

  • Re:  using Find in VBA

    In the MS code...
    If the data is not found then then object c is nothing.
    If c is nothing then it has no address and the line...
        "Loop While Not c Is Nothing And c.Address <> firstAddress"
    is trying to compare c.address (which doesn't exist) to a String.
    Excel/vba just won't compare items that are not the same type.
    (must be numbers vs. numbers, text vs. text, etc.)
    '---

    Try replacing the above line with two lines...
               If c Is Nothing Then Exit Do
            Loop Until c.Address = firstAddress
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Tuesday, August 4, 2015 3:57 AM
  • Can anybody explain or at least offer a work around.

    IMHO you'll find the function below helpful. Here is a simple sample how to use it:

    Sub FindIndustry()
      Dim R As Range
      Set R = FindAll(Range("C3:C96"), "Non-Profit")
      If R Is Nothing Then
        MsgBox "Not found"
      Else
        MsgBox "Found at " & R.Address(0, 0)
      End If
    End Sub

    Andreas.

    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As Object 'Dictionary
      Dim Temp() As Variant
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      'Initialize our internal stack
    #If Mac Then
      Set Stack = New Dictionary
      Stack.CompareMode = vbTextCompare
    #Else
      Set Stack = CreateObject("Scripting.Dictionary")
    #End If
    
      FirstAddress = c.Address
      Do
        Stack.Add Stack.Count, c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      Temp = Stack.Items
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    Tuesday, August 4, 2015 10:08 AM