Do While loop not incrementing properly in VBA

Question

I have written a small program to scan through a spreadsheet and calculate the total number of different item types based on a value in a column, then to print the results to specific column locations. My code is below

    Sub How_Many_items()

'define variables. A, B, C refer to letter coding. O is other. counter is for the count
    Dim A As Integer
    Dim B As Integer
    Dim C As Integer
    Dim O As Integer
    Dim Sum As Integer
    Dim Counter As Integer

'resetting variables
    A = 0
    B = 0
    C = 0
    O = 0
    Sum = 0
    Counter = 2
    'do while loop to continue going down rows until out of new data
    'if loops to accumulate values
    Worksheets("Values").Select

Do While Worksheets("Values").Cells(Counter, 54) <> Empty
    If Worksheets("Values").Cells(Counter, 54).Value = "A" Then
    A = A + 1 And Sum = Sum + 1 And Counter = Counter + 1

    ElseIf Worksheets("Values").Cells(Counter, 54).Value = "B" Then
    B = B + 1 And Sum = Sum + 1 And Counter = Counter + 1

    ElseIf Worksheets("Values").Cells(Counter, 54).Value = "C" Then
    C = C + 1 And Sum = Sum + 1 And Counter = Counter + 1

    ElseIf Worksheets("Values").Cells(Counter, 54).Value <> "A" Or "B" Or "C" Then
    Sum = Sum + 1 And Counter = Counter + 1
    End If

Loop

'print values in PivotTables worksheet

Worksheets(PivotTables).Cells(I, 20) = Sum
Worksheets(PivotTables).Cells(I, 21) = A
Worksheets(PivotTables).Cells(I, 22) = B
Worksheets(PivotTables).Cells(I, 23) = C


  End Sub

Based on looking at other information on this site, I don't actually need the worksheets().select action, but I don't think that is stalling the program.

When stepping through my program it stalls on the loop; the current data in use has "B" in the first column of interest, but it doesnt ever increment to the next row, which makes me think it isn't actually adding to the count or to B for some reason. The column that I am scanning is also a calculated column using a VLOOKUP, but I don't know if that affects what I am doing. The excel worksheet will crash if I try to run the program. Thanks for any help!

C = C + 1 And Sum = Sum + 1 And Counter = Counter + 1 is not how it's done... remove the And's and put each one on its own line. — braX
– braX, Commented Feb 27, 2020 at 14:51
Worksheets("Values").Cells(Counter, 54).Value <> "A" Or "B" Or "C" doesn't do what you think it does. — BigBen
– BigBen, Commented Feb 27, 2020 at 14:53
You don't need a loop here - just use Application.CountIf to get the counts of A, B, C. — BigBen
– BigBen, Commented Feb 27, 2020 at 14:59

SJR · Accepted Answer · 2020-02-27 15:01:34Z

1

Really more a comment but can't easily describe as such, but you need to split your actions into separate lines, viz

If Worksheets("Values").Cells(Counter, 54).Value = "A" Then
    A = A + 1
    Sum = Sum + 1
    Counter = Counter + 1

and ElseIf Worksheets("Values").Cells(Counter, 54).Value <> "A" Or "B" Or "C" Then

needs to be

ElseIf NOT(Worksheets("Values").Cells(Counter, 54).Value = "A" Or Worksheets("Values").Cells(Counter, 54).Value ="B" Or Worksheets("Values").Cells(Counter, 54).Value = "C") Then

You could use a With clause or a variable to shorten your code.

In fact your code could be simplified (I think) to

Sub How_Many_items()

'define variables. A, B, C refer to letter coding. O is other. counter is for the count
Dim A As Long
Dim B As Long
Dim C As Long
Dim O As Long
Dim Sum As Long
Dim Counter As Long

Counter = 2
'do while loop to continue going down rows until out of new data
'if loops to accumulate values
With Worksheets("Values")
    Do While .Cells(Counter, 54) <> Empty
        If .Cells(Counter, 54).Value = "A" Then
            A = A + 1
        ElseIf .Cells(Counter, 54).Value = "B" Then
            B = B + 1
        ElseIf .Cells(Counter, 54).Value = "C" Then
            C = C + 1
        End If
        sum = Sum + 1
        Counter = Counter + 1
    Loop
End With

Why not use COUNTIF formulas?

edited Feb 27, 2020 at 15:01

answered Feb 27, 2020 at 14:53

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

BigBen Over a year ago

Another idea: Select Case would be great here.

SJR Over a year ago

My last bit of logic might be wrong - someone clever please check!

SJR Over a year ago

Or agree with @BigBen - Select Case is easier to grasp.

SJR Over a year ago

@BigBen - agreed, have suggested a rewrite.

Sean C Over a year ago

This appears to have worked, but I have errors in my calculated column to fix. Thank you all so much for the help!

|

Mike · Accepted Answer · 2020-02-27 15:39:33Z

Does it have to be a Do While Loop? You could use the special cells function the get the last row containing data and just do a finite for loop.

Dim LastDataRow As Long 'Variable to store row number

LastDataRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For i = 1 To LastDataRow

If Worksheets("Values").Cells(Counter, 54).Value = "A" Then
A = A + 1 And Sum = Sum + 1 And Counter = Counter + 1

ElseIf Worksheets("Values").Cells(Counter, 54).Value = "B" Then
B = B + 1 And Sum = Sum + 1 And Counter = Counter + 1

ElseIf Worksheets("Values").Cells(Counter, 54).Value = "C" Then
C = C + 1 And Sum = Sum + 1 And Counter = Counter + 1

ElseIf Worksheets("Values").Cells(Counter, 54).Value <> "A" Or "B" Or "C" Then
Sum = Sum + 1 And Counter = Counter + 1
End If

Next i

Collectives™ on Stack Overflow

Do While loop not incrementing properly in VBA

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related