Macro with =SUM doesn't work

[leopardhawk]

Hi, I have been trying to create a simple macro and assign it to
"button" so that when the button is clicked, it will add up a colum
containing currency figures and place the total in a selected cell.
have had NO success so far and I am hoping someone can assist me.

I am using M/S Office Excel 2003 v. 11.6113.5703

I use <TOOLS><MACRO><RECORD NEW MACRO> and then click the OK button.
select cell S2 and record the following macro:

=SUM(N2:N84)

After I hit enter (which BTW, gives me the correct total), I push th
'stop recording' button.

I then select the 'button' from the toolbox and draw a button on th
worksheet. As soon as I let go with the mouse, the 'Assign Macro
window pops up and I assign the macro I just recorded to the button.

Now, since cell S2 contains the correct amount from when I recorded th
macro, I select S3 and push my new macro button. What happens is this:

As I move down column S, pushing the button as I come to each cell,
notice that in the 'function window' just below the toolbars, th
formula is changing by '1' with each click of the button. ie:

=SUM(N3:N85)
=SUM(N4:N86)
=SUM(N5:N87)...and so on.

This is creating a situation where, as I move down the worksheet, th
totals are all different and they should be the same!!

HELP

 

[Ron Coderre]

If you post the VBA code, it'll be easier for us to spot the problem.

Regards,
Ro

 

[leopardhawk]

As requested....

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/31/2005 by Mark
'

'
Range("S3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-5]:R[81]C[-5])"
Range("S4").Select
End Sub

 

[Tom Ogilvy]

Sub Macro6()
ActiveCell.Formula = "=Sum($N$2:$N$84)"
End Sub

--
Regards,
Tom Ogilvy

"leopardhawk" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Coderre]

Try this:

Sub Macro3()
ActiveCell.Formula = "=SUM(N2:N84)"
End Sub


Does that help?

Ron

 

[leopardhawk]

Well LO & BEHOLD! It works fine...

Thanks Ron...

I spent so much time on this and it is soooooo simple.

Well, I guess it helps to know programming.

Thanks again for your help.

Mark