using a variable in a VBA formula

Question

Can someone explain to me what the difference is in the following FormulaR1C1 lines? "sumcolumn" is Dim as Integer and the active cell in this example is V2. I can give you the full Macro code if it would help.

        sumcolumn = (ActiveCell.Column - 1) * -1

'        ActiveCell.FormulaR1C1 = "=SUM(RC[sumcolumn]:RC[-1])"

'        ActiveCell.FormulaR1C1 = "=SUM(RC[-21]:RC[-1])"

The second FormulaR1C1 line returns the correct response, but I want a "floating" sum, based on what has been input, such that the active cell may be in column A to column AA...

I am not understanding the difference.

Fr. Frank

Answer 1

If you want to embed a variable value into a formula, remember that the formula is just a text string, so you have to concatenate that value with the hard-coded text,like so

ActiveCell.FormulaR1C1 = "=SUM(RC[" & sumcolumn & "]:RC[-1])"

--

HTH

Bob

<Fr. Frank> wrote in message news:*** Email address is removed for privacy *** .com...

Can someone explain to me what the difference is in the following FormulaR1C1 lines? "sumcolumn" is Dim as Integer and the active cell in this example is V2. I can give you the full Macro code if it would help.

        sumcolumn = (ActiveCell.Column - 1) * -1

'        ActiveCell.FormulaR1C1 = "=SUM(RC[sumcolumn]:RC[-1])"

'      &n bsp; ActiveCell.FormulaR1C1 = "=SUM(RC[-21]:RC[-1])"

The second FormulaR1C1 line returns the correct response, but I want a "floating" sum, based on what has been input, such that the active cell may be in column A to column AA...

I am not understanding the difference.

Fr. Frank