Do Loop not returning first two rows of data in Range

[programsam]

Using this code to parse through values 'snumber' in a column and return another value. The issue is that it is not returning the first two values every time the list is updated. What am I missing?

With ws
    'lastrow = ws.Range("F").End(xlUp).Row
    lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    With ws.Range("C1:C" & lastrow)
        Set c = .Find(snumber, LookIn:=xlValues, Lookat:=xlWhole)
        'If Not c Is Nothing Then
        If c.Count < 8 Then
                FirstAddress = c.Address
            Do
                Worksheets("SD-LS").Range("F" & DestLR).Offset(30, 0) = c.Offset(0, 4)
                DestLR = DestLR + 1
                Set c = .FindNext(c)
            'Loop While Not c Is Nothing And c.Address <> FirstAddress
            Loop While DestLR <= 8 And c.Address <> FirstAddress
        End If
    End With
End With

 

[Micron]

I think that all one can suggest is to put a break point at the start, cause code to run and step through with F8 and examine the variable values - trouble shooting 101.
If you're not aware, you cannot check unassigned variable values; you have to move onto the next line. You can also inquire in the immediate window of the vb editor; e.g.
?DestLr and hit enter to see what value is returned. This can be done for virtually any object property as well.

Perhaps it is as simple as c not being what you think it should be (or similar).

 

[programsam]

I think that all one can suggest is to put a break point at the start, cause code to run and step through with F8 and examine the variable values - trouble shooting 101.
If you're not aware, you cannot check unassigned variable values; you have to move onto the next line. You can also inquire in the immediate window of the vb editor; e.g.
?DestLr and hit enter to see what value is returned. This can be done for virtually any object property as well.

Perhaps it is as simple as c not being what you think it should be (or similar).

@Micron, thank you for the response. I did learn something new with both the F8 and the Immediate Window. This script did work but when it did, it always skipped over the first two values that it should return. Now after changes, it's returning an 'Object Variable or With block variable not set' runtime error.

You can see above that 'c' is set and snumber does return a value when I mouse over it.

Apologies... still learning. Any more direction that you can provide is much appreciated.

 

[Micron]

You can see above that 'c' is set and snumber does return a value when I mouse over Nohow am I supposed to see both of those situations?

No, there's no way I can validate either of those claims since I can't do what you're doing. Set is for object variables only. With blocks require a With and End With statements. Maybe your object isn't set. Maybe your With or End With is either missing or out of sync with some other block, such as an IF block. You'd have to provide the modified code to provide any clues.

 

[programsam]

Just to update everyone, I managed to figure this one out.

The DestLR value was set to 1. The issue was that rows 1-3 in Column F were merged which threw off the cell count. The issue is that when I did the F8 walk through the first two values would display but then be overwritten by the next value in the loop so it would essentially display the first value, then loop through the second time and subsequently overwrite the second value in the same cell as the first. When it looped again, it overwrote value 2 with the third value and THEN finally started listing value 3 - 10 on separate rows.

The fix was to change the value of DestLR to 4 (not 1) and the Offset value of Worksheet 'SD-LS' to 29.

Problem fixed and all is well in the universe... for now.

Correct code below:

DestLR = 4

Set ws3 = Worksheets("PL")

Dim c As Range
Dim d As Range

With ws3
    lastrow = ws3.Cells(Rows.Count, "C").End(xlUp).Row
    With ws3.Range("C1:C" & lastrow)
        Set c = .Find(sNumber, LookIn:=xlValues, Lookat:=xlWhole)
        If Not c Is Nothing Then
                FirstAddress = c.Address
            Do
                Worksheets("SD-LS").Range("F" & DestLR).Offset(29, 0) = c.Offset(0, 4)
                DestLR = DestLR + 1
                Set c = .FindNext(c)
            Loop While c.Address <> FirstAddress
        End If
    End With
End With