Hi,
I want to modify this code to change "J" to a variable value
ActiveWKS.Range("E3").Formula = "=INDEX(Raw_Data!J8:J100,MATCH(Reconciliation!C2:C89,Raw_Data!A8:A88))"

A friend suggested this approach. I'm wondering if there is a cleaner, simpler one:

(pseudo code)
Function ColLtr(iCol As Long) As String
ltr = “”
If iCol > 0 And iCol <= Columns.Count Then
ltr = Evaluate("substitute(address(1, " & iCol & ", 4), ""1"", """")")
End If
pos = InStr(1, ltr, ":")
If pos > 0 Then
ltr = Left(ltr, pos - GC_ONE)
End If
ColLtr = ltr
End Function

So you would do something like:
Dim colNo as Long
Dim retVal as String
……
retVal = ColLtr(colNo)
ActiveWKS.Range("E3").Formula = "=INDEX(Raw_Data!” & retVal & “8:” & retVal & “100,MATCH(Reconciliation!C2:C89,Raw_Data!A8:A88))"