FormulaR1C1 Help

[danomaniac]

Hey Everyone,
I'm trying to insert a formla via code. I have a database query that returns some data and then my code deletes rows based on user selected criteria. This results in an unknown number of rows each time. I can count the resulting rows via code, but can't figure out how to transfer that variable data into my FormulaR1C1 statement. This is what I have, and obviously, the variable won't work in the statement, but that's where I need it. Any ideas? Thanks very much for any help.

Cells(2, 1).Activate
Selection.End(xlDown).Activate
lastrow = ActiveCell.Row
Cells(lastrow + 2, 12).Activate
ActiveCell.FormulaR1C1 = "=(SUM(R[newlastrow]C:R[-2]C))"

 

[danomaniac]

Thanks anyway, but I found another way...

I just created a variable, then cycled through each row that existed and added the cell I needed, then put the total at the bottom.

 

[DaleWatson123321]

Hi danomaniac,

Here's the method I would use. It's been tested, and of course it works.

Sub Insert_Formula()
Cells(2, 1).Activate
firstcell = ActiveCell.Address
Selection.End(xlDown).Activate
lastrow = ActiveCell.Row
lastcell = ActiveCell.Address
addrange = firstcell & ":" & lastcell
Range(addrange).Name = "range1"
Cells(lastrow + 2, 12).Activate
ActiveCell.FormulaR1C1 = "=SUM(range1)"
End Sub

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca