trying to insert a formula

[davegb]

I'm trying to put a formula in a cell that sums from the row
immediately above to a row whose number I saved previously as a
variable (lPrevSumRow):

With ActiveSheet
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(.Cells(lNextRow - 1,
9).Cells(lPrevSumRow, 9))"

End With

I have figured out the putting the formula in quotes isn't working
because I'm getting an error on the Sum function. XL isn't recognizing
the (.cells(lNextRow....) while it's in quotes. Is there anyway to
insert the formula with the range to be summed as a variable rather
than actual cell addresses?

Thanks!

 

[Gary Keramidas]

i set the variables because i didn't know what yours were

With ActiveSheet
lPrevSumRow = 1
lNextRow = 15
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow & ":" &
"i" _
& lNextRow - 1 & ")"
End With

 

[Gary Keramidas]

watch the word wrap and the underscores (there aren't any in the formulas) ,
i used vista to post this and windows mail isn't too polished yet.

..Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow & ":" & "i" &
lNextRow - 1 & ")".Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":" & "i" & lNextRow - 1 & ")"

 

[davegb]

watch the word wrap and the underscores (there aren't any in the formulas) ,
i used vista to post this and windows mail isn't too polished yet.

.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow & ":" & "i" &
lNextRow - 1 & ")".Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":" & "i" & lNextRow - 1 & ")"

The one above worked. The second one you submitted didn't. Not sure why
you had .formula in it twice, maybe a pasting mistake?

I made one small change. Where you had &":"&"i", I replaced with &":i".

Thanks for the help!