loop and sum

[daveyc18]

Hello... i have a file in column A contains

"CURRENCY TOTAL: C$" mulitple times (eg A23)


-then I'm gonna make a sum formula from that point, but going 6 columns to the RIGHT and one row DOWN (in this case, the formula would be G24)

-in G24, it's gonna sum 2 numbers to the LEFT of it (in this case, F24 and F23)


...


could someone help me create a loop to look at all the "CURRENCY TOTAL: C$" in column A and then dynamically make the sum formula accordingly?

 

[JoeMo]

I think this does what you want, but its untested .

Sub daveyc18()
Dim Fnd As Range, fAdr As String
With Range("A:A")
    Set Fnd = .Find("CURRENCY TOTAL: C$", , xlFormulas, xlPart, , , True)
    If Not Fnd Is Nothing Then
        fAdr = Fnd.Address
    Else
        MsgBox "search string not found in col A"
        Exit Sub
    End If
    Do
        Fnd.Offset(1, 6).Formula = "=SUM(" & Fnd.Offset(0, 5).Resize(2, 1).Address & ")"
        Set Fnd = .FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
    Loop
End With
End Sub

 

[daveyc18]

I think this does what you want, but its untested .

Sub daveyc18()
Dim Fnd As Range, fAdr As String
With Range("A:A")
    Set Fnd = .Find("CURRENCY TOTAL: C$", , xlFormulas, xlPart, , , True)
    If Not Fnd Is Nothing Then
        fAdr = Fnd.Address
    Else
        MsgBox "search string not found in col A"
        Exit Sub
    End If
    Do
        Fnd.Offset(1, 6).Formula = "=SUM(" & Fnd.Offset(0, 5).Resize(2, 1).Address & ")"
        Set Fnd = .FindNext(Fnd)
        If Fnd Is Nothing Then Exit Do
        If Fnd.Address = fAdr Then Exit Do
    Loop
End With
End Sub

worksgreat, thanks...had to change to xlvalues isntead of formulas for whatever reason

 

[JoeMo]

worksgreat, thanks...had to change to xlvalues isntead of formulas for whatever reason

You are welcome - thanks for the reply. Most likely reason you had to change xlFormulas to xlValues is that your search term "CURRENCY TOTAL: C$" is in cells containing formulas. If that's the case, be sure to tell us in your initial post that some or all the cells holding the search term are formulaic cells!