ActiveCell.FormulaR1C1 with a variable

[mmetzinger]

OK, so I recorded the following formula in excel but I need to change it so that it uses a variable for the column instead of a fixed column

Currently I have:

ActiveCell.FormulaR1C1 = "=SUM('Inbound Calls Handled'!C[-1])"

I need something like:

    ActiveCell.FormulaR1C1 = "=SUM('Inbound Calls Handled' CallColumn [-1])"

CallColumn is a string variable that holds a column letter that may shift depending on how the user has arranged their sheet. This is why I need the formula to be dynamic as well.

Thanks for any help!

 

[Scott Huish]

If you want to put the answer in a specific place, you do not need to use ActiveCell.

However, what does your string contain?

You could also just give a name to the Column, then if it moves it won't affect the formula.

Sub test()
Dim x As Long, c As Range
Set c = Range("namedrange")
x = c.Cells(Rows.Count).End(xlUp).Row
c.Offset(, 1).Rows(x) = Application.WorksheetFunction.Sum(c)
End Sub

 

[mmetzinger]

I need a cell on Sheet(6) to be able to sum a column on Sheet(2) that may be in a different spot each time. I don't want any type of calculation going on Sheet(2). I just want the cell on Sheet(6) to have a sum of all values for the column letter than is stored in variable "CallColumn".

My code is pages and pages long and these variables are set way back at the beginning of the code and I have no intention on trying to change them and break every other line of code in my project.

isn't there just like an easy way of saying like:

activecell.formula = "=SuM('Inbound Calls Handled' CallColumn [-1])"</pre>

 

[Scott Huish]

Maybe, I was just trying to help.

But in order for me to answer that, can you please answer my question about what is stored in your variable:

"A" "A:A" ? What?