Enter formula in cell

[Guest]

I know this is simple but I am not sure of the syntax to do this, here is what I hav

LastActiveRowForNames = Cells(Rows.Count, "B").End(xlUp).Ro
Range("G" & LastActiveRowForNames + 1).Selec
ActiveCell.FormulaR1C1 = "=SUM(R[-LastActiveRowForNames]C:R[-1]C)" <---problem her

In this I a basically putting a sum at the bottom row of column "G" to sum it. I won't know what the bottom row is so I have it set as "LastActiveRowForNames" then I add one to it to make the active cell the one at the bottom of column G. Then I want to sum it but it doesn't like what I have. I know the idea is right, I think I am just wrong on syntax.

Any help would be greatly appreciated, I know its simple, I just don't know the answe

Jim

 

[Bob Phillips]

Jim,

Try this

LastActiveRowForNames = Cells(Rows.Count, "B").End(xlUp).Row
Range("G" & LastActiveRowForNames + 1).FormulaR1C1 = _
"=SUM(R[-" & LastActiveRowForNames & "]C:R[-1]C)"

But why put it in column G but test last row in column B?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I know this is simple but I am not sure of the syntax to do this, here is what I have

LastActiveRowForNames = Cells(Rows.Count, "B").End(xlUp).Row
Range("G" & LastActiveRowForNames + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-LastActiveRowForNames]C:R[-1]C)" <---problem here

In this I a basically putting a sum at the bottom row of column "G" to sum

it. I won't know what the bottom row is so I have it set as
"LastActiveRowForNames" then I add one to it to make the active cell the one
at the bottom of column G. Then I want to sum it but it doesn't like what I
have. I know the idea is right, I think I am just wrong on syntax.

 

[Brad]

When including variable names in formulas or strings you
want to place in the worksheet, you need to refer to them
properly. Your code looks good, with the one correction
below:

ActiveCell.FormulaR1C1 = "=SUM(R[-LastActiveRowForNames]C:R
[-1]C)"

Needs to be:

ActiveCell.FormulaR1C1 = "=SUM(R[-" &
LastActiveRowForNames & "]C:R[-1]C)"

I personally can't make sense out of the R1C1 reference
system, so I can't help your code other than by making
this one correction.

HTH.

-----Original Message-----
I know this is simple but I am not sure of the syntax to do this, here is what I have

LastActiveRowForNames = Cells(Rows.Count, "B").End (xlUp).Row
Range("G" & LastActiveRowForNames + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-

LastActiveRowForNames]C:R[-1]C)" <---problem here

In this I a basically putting a sum at the bottom row of

column "G" to sum it. I won't know what the bottom row is
so I have it set as "LastActiveRowForNames" then I add one
to it to make the active cell the one at the bottom of
column G. Then I want to sum it but it doesn't like what
I have. I know the idea is right, I think I am just wrong
on syntax.

Any help would be greatly appreciated, I know its

simple, I just don't know the answer

 

[Guest]

Thanks to both of you. Works great and I think I understand entering variables into formulas better now.

Thanks,

Jim