Finding the sum of numbers in VBA

**csr_cafc** · 10-21-2010, 07:40 AM

Silly question,

I have the following for loop:

Dim i As Integer, count As Integer

count = 0

For i = 1 To 10
count = count + 1
Next i

Now im not sure if i need the loop or not but how would i calculate the sum of the numbers that the loop passes i.e. 1,2,3,4,5 etc.

**DonkeyOte** · 10-21-2010, 07:45 AM

There are a few ways:

count = Evaluate("SUM(ROW(1:10))")

Generally speaking it's better to use Longs than Integers for reasons not worth going into here (else use Byte where valid)

**csr_cafc** · 10-21-2010, 07:48 AM

Sorry should have explained properly; if i say wanted the user to input the values (from 1 to n as opposed to 1 to 10). This needs to be done purely in vba and not invlolving the spreadsheet part at this stage.

**DonkeyOte** · 10-21-2010, 07:49 AM

Modify

count = Evaluate("SUM(ROW(1:" & nVariable & "))")

the above does not write to the worksheet in any way - it's a simple means of calculating what you want without need for iteration.

**csr_cafc** · 10-21-2010, 09:27 AM

Ok so i have entered that into my code as follows:

Public Sub SumFirstN_Numbers()
Dim i As Integer, count As Integer
Dim nVariable As Integer

nVariable = InputBox("Choose a number other than 1. ")

count = 0

For i = 1 To 10
    count = count + 1
Next i

count = Evaluate("SUM(ROW(1:" & nVariable & "))")

MsgBox ("The sum of the numbers from 1 to " & nVariable & " is. " & count & " .")

End Sub

Ok this seems to work but am i right in assuming that it does not use the for loop?

If so how can you get the same result but using the for loop which i think was the aim of the exercise that i was given.

Thanks for all your help so far.

**DonkeyOte** · 10-21-2010, 09:32 AM

Your running count should increment by i rather than 1.

(and presumably you should be To nVariable rather than 10 ... you should use Application.InputBox)