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!

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

"davegb" wrote in message
oups.com...
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!

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 & ")"
--


Gary

"Gary Keramidas" wrote in message
...
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

"davegb" wrote in message
oups.com...
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 wrote:
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 & ")"
--


Gary

"Gary Keramidas" wrote in message
...
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

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!


"davegb" wrote in message
oups.com...
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!