CODEWITHSUNDEEP
vbaloopsdo-while
Sean C
Sean C

Reputation: 3

Do While loop not incrementing properly in VBA

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!

Upvotes: 0

Views: 696

Answers (2)

Mike
Mike

Reputation: 367

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

Upvotes: 0

SJR
SJR

Reputation: 23081

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?

Upvotes: 1

Related Questions