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!
