passing a variable to a function

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))"

Originally Posted by Kaper

Just

ActiveWKS.Range("E3").Formula = "=INDEX(Raw_Data!" & range("A8:A100").offset(0, iCol-1).address & ",MATCH(Reconciliation!C2:C89,Raw_Data!A8:A88))"

shall do.

Please note how code tags improve readability of the post and edit your post to comply with forum rules http://www.excelforum.com/forum-rule...rum-rules.html

Sorry - I tried and they didn't show in the preview, so I thought maybe the board didn't use them. Maybe I did something wrong.