Excel Programming Help Sum formula

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.

• Status: Open
• Question Views: 1112
• Answer Count: 6
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Simmy C-me

Go To Solution

Answered By AngelOfTech 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

 

 

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Schumacher 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?

About Schumacher

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Simmy C-me 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)

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By AngelOfTech 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

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Schumacher 230 points N/A #97747

Excel Programming Help Sum formula

Thanks a lot, both of you.

That was great!

About Schumacher

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By AngelOfTech 5 points N/A #97748

Excel Programming Help Sum formula

You are welcome.

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel