Loop Without Do Error

[alexa]

I have a mixed list of people and their sales figures which looks alot like this:

http://img432.imageshack.us/my.php?image=excelhp4.gif

I wrote the following to try and sum the individuals sales figures and place their totals into the correct cells.

----------------------------------------------------

Sub Totaler()
Dim TotalA As Double
Dim TotalB As Double
Dim TotalC As Double
Dim TotalD As Double
TotalA = 0
TotalB = 0
TotalC = 0
TotalD = 0
Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Value = Range("a16").Value Then
TotalA = TotalA + ActiveCell.Value
If ActiveCell.Value = Range("a17").Value Then
TotalB = TotalA + ActiveCell.Value
If ActiveCell.Value = Range("a18").Value Then
TotalC = TotalA + ActiveCell.Value
If ActiveCell.Value = Range("a19").Value Then
TotalD = TotalA + ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("b16").Value = TotalA
Range("b17").Value = TotalB
Range("b18").Value = TotalC
Range("b19").Value = TotalD
End Sub

----------------------------------------------------

Problem is every time I execute the macro I get a Loop Without Do Error, and I can figure out why. Any help you can give would be appreciated.

Also is there any way to do this for a larger number of people without having to create a seperate total variable for each person?

[starryknight64]

Try swapping the "Do" with the "Loop".

[mudraker]

You need to EndIf each If statement or use ElseIF

You could lso try usung Select Case

Do Until ActiveCell.Value = Empty
If ActiveCell.Value = Range("a16").Value Then
TotalA = TotalA + ActiveCell.Value
End If
If ActiveCell.Value = Range("a17").Value Then
TotalB = TotalA + ActiveCell.Value
End If
If ActiveCell.Value = Range("a18").Value Then
TotalC = TotalA + ActiveCell.Value
End If
If ActiveCell.Value = Range("a19").Value Then
TotalD = TotalA + ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop


OR


Do Until ActiveCell.Value = Empty
If ActiveCell.Value = Range("a16").Value Then
TotalA = TotalA + ActiveCell.Value
ElseIf ActiveCell.Value = Range("a17").Value Then
TotalB = TotalA + ActiveCell.Value
ElseIf ActiveCell.Value = Range("a18").Value Then
TotalC = TotalA + ActiveCell.Value
ElseIf ActiveCell.Value = Range("a19").Value Then
TotalD = TotalA + ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop

Select Case ActiveCell.Value
Case Range("a16").Value
TotalA = TotalA + ActiveCell.Value
Case Range("a17").Value
TotalB = TotalA + ActiveCell.Value
End Select

[Myles]

Alex,

In using the IF...THEN construct, there is one point of difference to note. In both

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

all 3 conditions are evaluated, whereas in

Please Login or Register  to view this content.

only one condition is evaluated at a time. The ElseIf format operates like SELECT CASE.

Given the nature of your post, you will be better of using SELECT CASE construct as Mudraker suggested or, if you want a turn at IF..THEN, use the Elseif option. When you force all the conditions to be evaluated in a loop, you unnecessarily slow down execution. Parsimony is the watchword in programming.