Excel Programming Help Sum formula

Asked By 230 points N/A Posted on -
qa-featured

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
Best Answer by Simmy C-me
Answered By 0 points N/A #97743

Excel Programming Help Sum formula

qa-featured

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

qa-featured

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?

Best Answer
Best Answer
Answered By 0 points N/A #97745

Excel Programming Help Sum formula

qa-featured

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 0 points N/A #97746

Excel Programming Help Sum formula

qa-featured

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

qa-featured

Thanks a lot, both of you.

That was great!

Answered By 0 points N/A #97748

Excel Programming Help Sum formula

qa-featured

You are welcome.

Login/Register to Answer

Related Questions