CODEWITHSUNDEEP
excelvbaloopscounter
HJJ
HJJ

Reputation: 13

Using a counter in a loop within a loop = problems in excel vba

Hi thanks for your help.

Im a total noob at excel but I have been teaching myself due to my research needs
I created this basic code by adapting it from the youtube channel ExcelVbaIsFun 

Sub ZipCodeSum2()

Dim dbsheet As Worksheet
Set dbsheet = ThisWorkbook.Sheets("Sheet1")
lr = dbsheet.Cells(Rows.Count, 1).End(xlUp).Row
SelRow = Selection.Row
'zip code
zip = dbsheet.Cells(SelRow, 2)

For i = 2 To lr 'change lr to 3 for testing purposes
Cells(i, 3).Select

 For x = 2 To lr 'change lr to 10 for testing purposes
    If dbsheet.Cells(x, 2) = zip Then
        counter = counter + dbsheet.Cells(x, 1)
    ActiveCell = counter
    End If

 Next x

Next i

End Sub

The problem is that for the first 'i' "outer loop" runs fine and the sum function works well. However, in the next 'i' it adds the result of the first 'i' sum and then adds the second 'i' sum to it as well. So if the zip codes are the same, it basically results in the second 'i' being double the result that I want.

Any help would be appreciated! Thanks!

sample of my worksheet

Upvotes: 1

Views: 1330

Answers (2)

user4039065
user4039065

Reputation:

In C2 try,

=sumifs(a$2:a2, b$2:b2, b2)

Fill down as required.

If you only want the last instance of a value to show the sum total then,

=IF(COUNTIF(A$2:A2, A2)=COUNTIF(A:A, A2), SUMIFS(A$2:A2, B$2:B2, B2), TEXT(,))

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

If you want counter to be reset before the inner loop is executed, initialise it before entering that loop:

Sub ZipCodeSum2()
    Dim dbsheet As Worksheet
    Dim lr As Long
    Dim SelRow As Long
    Dim zip As String
    Dim i As Long
    Dim counter As Currency
    Dim x As Long

    Set dbsheet = ThisWorkbook.Sheets("Sheet1")
    lr = dbsheet.Cells(dbsheet.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lr 'change lr to 3 for testing purposes
        'zip should be set inside the "For i" loop
        zip = dbsheet.Cells(i, "B").Value
        counter = 0
        For x = 2 To lr 'change lr to 10 for testing purposes
            If dbsheet.Cells(x, "B").Value = zip Then
                counter = counter + dbsheet.Cells(x, "A").Value
            End If
        Next 
        dbsheet.Cells(i, "C").Value = counter
    Next i
End Sub

But this can be simplified to:

Sub ZipCodeSum2()
    With ThisWorkbook.Sheets("Sheet1")
        With .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            .Formula = "=SUMIF(B:B,B2,A:A)"
            .Value = .Value
        End With
    End With
End Sub

Or you could just place the formula

=SUMIF(B:B,B2,A:A)

into cell C2 in Excel and copy it down.

Upvotes: 1

Related Questions