CODEWITHSUNDEEP
excelvba
LE FE
LE FE

Reputation: 45

for next inside a do while loop - not fully functioning

I've written a do while-loop that works well, but when I try to add a for each-next loop inside it, it stops after only the first do while-loop. I'm really not sure what I need to add/remove to get back to a functioning loop

rgData, rgHeader, and RowSum are ranges that are defined earlier in my code

Dim myCell As Range, c As Range, firstAddress As String
Const strFindMe As String = "Index"

    With rgData
        Set c = rgHeader.Find(what:=strFindMe, Lookat:=xlPart).Offset(1, 0)
        If Not c Is Nothing Then
            firstAddress = c.Address
                Do
                    Dim ColIndex As Range
                        Set ColIndex = Range(c.Address, Cells(Range(c.Address).Offset(MktCt - 1, 0).Row, Range(c.Address).Column))
                            For Each myCell In ColIndex
                                myCell.FormulaR1C1 = "=IF(RC[-3]<>"""",RC[-3]/R" & RowSum & "C[-3]*100,"""")"
                                myCell.NumberFormat = "0"
                            Next
                        Set ColIndex = Nothing
                        Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

the reason I'm attempting to write the code this way, is because the report I receive is already pivoted, and so there can be several "index" columns required for multiple demographic measures

currently this is working for the first "index" column, but isn't moving to the next "index" column.

Any ideas would be very helpful, thanks

Upvotes: 0

Views: 66

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Your first Find is against rgHeader but your .FindNext references rgData (via the With block)

To simplify the logic I would separate the Find from the processing:

Dim matches as Collection, m

Set matches = FindAll(rgHeader, strFindMe) 
For Each m in matches
    'process m
Next m

Separate function to perform the find:

Public Function FindAll(rng As Range, val As String) As Collection

    Dim rv As New Collection, f As Range
    Dim addr As String

    Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then addr = f.Address()

    Do Until f Is Nothing
        rv.Add f
        Set f = rng.FindNext(after:=f)
        If f.Address() = addr Then Exit Do
    Loop

    Set FindAll = rv
End Function

Upvotes: 3

Related Questions