Variable in R1C1 formula pasted in Excel worksheet 2

[Numbers1]

Is it possible to place a variable, that is defined in a module, into a formula that is pasted into a cell on a worksheet?
Dim intColRef as Interger
intColRef = 5
ActiveCell.Offset(0, 6).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[5]"
Instead
ActiveCell.FormulaR1C1 = "=RC[intColRef]"

Can the value of the variable be carried out to this formula in the cell?
Thanks, Numbers

 

[PCLewis]

uSe a funtion wrapper for the variable. You might find this kind of function called an Accessor in some terminology.

' Assuming intColRef is definied as a global
Public Function GetIntColRef() As String
  GetIntColRef = intColRef 
End Function

Then in your cell, you can use ActiveCell.FormulaR1C1 = "=RC[" & GetIntColRef & "]"

Well, I don't know for sure since the last Excel macro I wrote was a million years ago, but the theory is sound

 

[PHV]

Simply use this in your code:
ActiveCell.FormulaR1C1 = "=RC[" & intColRef & "]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Numbers1]

Thanks for the help. This works well.
Numbers