Asked By
Schumacher
230 points
N/A
Posted on - 05/16/2011
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.

## 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

## 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?

## 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)

## 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

## Excel Programming Help Sum formula

Thanks a lot, both of you.

That was great!