Do...Loop???

[boxboy30]

How do I loop the following formula. If A2=A1 then sum(C2+C1)...keep that going until it is false. I've written the following code but I have to keep writing an IF statement in order for it to keep comparing the A column.

Sub Macro_test()
If [a2].Text = [a1].Text Then
[c1].Value = [b2].Value + [b3].Value
If [a3].Text = [a1].Text Then
[c1].Value = [b3].Value + [b2].Value + [b1].Value

Else: End If
Else: End If

End Sub

how do I keep it going without manually writing it?

 

[cstimart]

Will this work?

Sub macro_test()
Dim LR As Long
Dim i As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("A" & i + 1) = Range("A" & i) Then
        Range("C" & i) = Application.Sum(Range("B" & i + 1 & ":B" & i + 2))
    End If
    If Range("A" & i + 2) = Range("A" & i) Then
        Range("C" & i) = Application.Sum(Range("B" & i & ":B" & i + 2))
    End If
Next i
End Sub

 

[boxboy30]

The problem is that sometimes there are 10 cells in column A that are equal to A1...I need the same 10 cells in column B to sum.

A1: B1: C1 should result in:
s : 5: 26
s :7
s :10
s :1
s :3
t :100: 104
t :4
y :80: 84
y :4

that's why I was thinking do/loop...any suggestions

 

[cstimart]

Try this...

Sub macro_test()
Dim TR As Long
Dim LR As Long
Dim i As Long

TR = 1
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LR + 1
        If Range("A" & i) <> Range("A" & i - 1) Then
            Range("C" & TR) = Application.Sum(Range("B" & TR & ":B" & i - 1))
            TR = i
        End If
    Next i
End Sub

 

[boxboy30]

This kind of works...it starts off really accurate, then it drifts to not being so correct...like:

A1: B1: C1:
s : 5: 26
s :7
s :10
s :1: 4 INCORRECT
s :3
t :100: 104
t :4
y :80: 84
y :4

Is it because it refering back to A1? or is it refering back to the cell above it?

 

[cstimart]

This kind of works...it starts off really accurate, then it drifts to not being so correct...like:

A1: B1: C1:
s : 5: 26
s :7
s :10
s :1: 4 INCORRECT
s :3
t :100: 104
t :4
y :80: 84
y :4

Is it because it refering back to A1? or is it refering back to the cell above it?

It's referring to the cell above in the calculation...is there an extra space in A4 and A5?

 

[boxboy30]

Silly me! I had the filter on! This is awesome...so can you explain the code to me so I can understand it. I want to now add some variables to it (including other cells)...if(D1 = "Cancelled", then don't include), etc.

You're awesome by the way!

 

[boxboy30]

and can i make it filter by month...so it will only for every month it starts over...month being in column E?

 

[cstimart]

Basically, the TR is the indicator of the Top Row and i loops thru to find where Column A changes...it then sum from the top row to the bottom of that group. It then Changed TR to be the new top row and repeats thru the end of the cells in A with a value.

 

[Scott Huish]

Your original question can be done without a loop:

Sub test()
With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(COUNTIF($A$1:A1,A1)=1,SUMIF(A:A,A1,B:B),"""")"
    .Value = .Value
End With
End Sub