## Excel Programming Help Sum formula

Asked By 230 points N/A Posted on -

Hi

I have been playing around with Excel a few days. It is really a nice tool. I can do a lot of calculations here with less effort. I have also heard that it is possible to do some programming with excel too. So i thought i will try.

Here is what I want to do first.

My present sheet has 7 columns each are having a value (cash amount) in each of them. The 8th column holds the following equation:

=SUM(A1:C1)/3+SUM(D1:E1)/2+F1+G1

So it gives the summation of the left 7 columns. The equation changes from A1 to A25 , depending on the row number.

The problem is that it is so often I accidently erase the equation written in the field. So i just want the equation to be hidden. i. e. when I press a button the calculation will be done. Will I code the equation in the program? How do I do it?

I have a little experience on C so I think it would be easy.

SHARE
Answered By 5 points N/A #97743

## Excel Programming Help Sum formula

You also need to specify a break condition for the loop. For example:

Dim r As Integer
r = 1
Do
If Sheet4.Cells(r, 1) = "" Then                                          ' I assumed that your first column always holds a value
Exit Do
Else
Sheet4.Cells(r, 8) =  ' your formula here
End If
r = r + 1
Loop

Answered By 230 points N/A #97744

## Excel Programming Help Sum formula

Here is my code:

Private Sub CommandButton1_Click()
Dim r As Integer
r = 1
Do
If Sheet4.Cells(r, 1) = "" Then
Exit Do
Else
Sheet4.Cells(r, 8) =SUM(Ar:Cr)/3+SUM(Dr:Er)/2+Fr+Gr

End If
r = r + 1
Loop
End Sub

But it is not working. Do I need to change the equation? How do I specify Ar instead of A1?

Answered By 0 points N/A #97745

## Excel Programming Help Sum formula

Use this

Sheet4.Cells(r, 8) = (Sheet4.Cells(r, 1) + Sheet4.Cells(r, 2) + Sheet4.Cells(r, 3)) / 3 + (Sheet4.Cells(r, 4) + Sheet4.Cells(r, 5)) / 2 + Sheet4.Cells(r, 6) + Sheet4.Cells(r, 7)

Answered By 5 points N/A #97746

## Excel Programming Help Sum formula

Here is the full code with corrections:

Private Sub CommandButton1_Click()
Dim r As Integer
r = 1
Do
If Sheet4.Cells(r, 1) = "" Then
Exit Do
Else
Sheet4.Cells(r, 8) =(Sheet4.Cells(r, 1) + Sheet4.Cells(r, 2) + Sheet4.Cells(r, 3)) / 3 + (Sheet4.Cells(r, 4) + Sheet4.Cells(r, 5)) / 2 + Sheet4.Cells(r, 6) + Sheet4.Cells(r, 7)

End If

r = r + 1
Loop

End Sub

Answered By 230 points N/A #97747

## Excel Programming Help Sum formula

Thanks a lot, both of you.

That was great!

Answered By 5 points N/A #97748

You are welcome.