Macros with formula

[Rick DeLa]

Help! I am recording a macro and here is my delema: I need to add a1 and a2 to get totals. I record the macro and lets say data only goes up to row 10. The macro stops at row 10. Problem is that I export data to this workbook and it coulb be more that 10 rows! but my macro only goes to ten. Is there a way to tell it (formula)if there is data sum it.

Your help will be greatly appreciated!!

Let me start over I need to put a formula in j2 that will sum o2:p2 the 02:p2 will have different data all the time (sometimes it call go all the way to row 10 or row 100 I need a formula on j2 that can sum those three columns all the time. I am creating a macro.

[ This Message was edited by: Rick DeLa on 2002-05-08 15:49 ]

[s-o-s]

On 2002-05-08 15:18, Rick DeLa wrote:
Help! I am recording a macro and here is my delema: I need to add a1 and a2 to get totals. I record the macro and lets say data only goes up to row 10. The macro stops at row 10. Problem is that I export data to this workbook and it coulb be more that 10 rows! but my macro only goes to ten. Is there a way to tell it (formula)if there is data sum it.

Your help will be greatly appreciated!!

Rick, there's not enough info, can you include the macro code and also some of your data, its not very clear what you are trying to sum.

[NateO]

Something like:

Sub sum()
MsgBox Application.sum([a1:a65536])
End Sub

??

[Cosmos75]

This'll work. BUT it sums from A1 to LAST CELL in Column A that has data (Any kind of data)

Sub SumColumnA()

'Selects Range from A1 to LAST cell in column A with data
Range("A1", Range("A65536").End(xlUp).Address).Select

'Insert SUM Formula to sum selection that was selected above
Range("A65536").End(xlUp).Offset(1, 0).Formula = "=SUM(" & Selection.Address & ")"

End Sub


Hope this helps!

EDIT: If there is no data, it'll just show a zero in A1.


[ This Message was edited by: Cosmos75 on 2002-05-08 15:37 ]