Nested Do While

Question

I am trying to run code that does the following:

1) Find record with matching index number

2) In row that match was found, check conditions

3) If conditions are satisfied, then record an index number stored in a column within the same row as a new variable

4) Find record with the matching index number stored from 3)

5) evaluate the row of the record for conditions

6) If conditions are satisfied, then I want to fill data from i = 0 until no more matches are found

7) check the next match in the loop started in 4)

8) If no more matches are found in 4), repeat the loop started in 1)

I have previously made VBA that would essentially evaluate 4) through 7), which works. Trying to essentially nest the loop within itself has been where my errors started.

Here is the code that I'm trying to run:

i = 0
With Worksheets(ReferenceSheet).Range("A1:A" & endrow)
Set c = .Find(CIFNumber, LookIn:=xlValues)

If Not c Is Nothing Then
firstaddress = c.Address

Do
' e = Worksheets(ReferenceSheet).Range("B" & .FindPrevious(c).Row)
  j = CStr(c.Row)
  CIFRelationshipType = Worksheets(ReferenceSheet).Cells(j, "G").Value
  GCIFNumber = Worksheets(ReferenceSheet).Cells(j, "E").Value
If CIFRelationshipType = "G" Then

        Set b = .Find(GCIFNumber, LookIn:=xlValues)
        If Not b Is Nothing Then
        firstbaddress = b.Address
        Do
    matchedj = CStr(b.Row)
    GCIFRelationshipType = Worksheets(ReferenceSheet).Cells(matchedj, "G").Value
If GCIFRelationshipType = "P" Then

i = i + 1
'determine which input loop we are on then populate accordingly.

Populate data here for each i

End If
    Set b = .FindNext(b)
If b Is Nothing Then
End If
Loop While b.Address <> firstbadress
End If

End If
  Set c = .FindNext(c)
If c Is Nothing Then
End If

Right here, I get

error 91 "Object variable or With block variable not set"

Loop While c.Address <> firstaddress
End If
End With

If someone walked up to you and asked " Why is this that way" your first thought would be what is "this" and "that way". So saying you have errors but not actually telling anyone what they are is the same as why is this that way. Provide what you expect, what is happening and details about any errors. — Sorceri, Mar 16 '18 at 16:20
Sorry about that.... edited post to add more details about where I hit the error. — Chase Meadows, Mar 16 '18 at 16:46

score 0 · Answer 1 · answered Mar 16 '18 at 18:39

you checked if c has an object or not: 'if c is nothing' and you ended your statement 'end if' then you put the 'c' object in the loop condition 'Loop While c.Address <> firstaddres' if, at that moment, the 'c' object is nothing, it will give an error

the error you mentioned happens every time an object variable is nothing or you forgot to use the 'with' clause

Nested Do While

1 Answers1