Inserting a Range Into A Formula Using a Variable

[username142]

I've declared the variable 'Action' as a string, and I am assigning a range to it based upon the contents of a cell. For example, the first one being assigned is C52:CY55. And I'm trying to insert that range into the formula below using the variable name, but it's not working. In the formula that's inserted to the active cell, the range is posting as $AZ:$AZ:'CY55' instead. Does anyone have any suggestions?

ActiveCell.Formula = _
            "=SUM(INDEX('" & CurrentSheet & "'!" & Action & ",,COLUMN()+Summary!RC[-1]-2))"

 

[JLGWhiz]

Try inserting 'Action.Address' instead of just 'Action' The Excel formula is looking for a cell reference, not a variabele value.

 

[username142]

Thanks for the suggestion. I'm getting "Compile error: invalid qualifier" with that change.

 

[Norie]

You can't mix A1 and R1C1 notation.

This part of your formula uses R1C1

Summary!RC[-1]-2

and in your post you appear to be saying Action is a string variable with a range reference in A1 notation.

 

[username142]

Thank you - I removed Summary!RC[-1]-2, and that was the problem.