passing a variable to a function

[expat1000]

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

[Kaper]

Just

Please Login or Register  to view this content.

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

[expat1000]

Just

Please Login or Register  to view this content.

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.

[Kaper]

Please do read (and follow) forum rules: http://www.excelforum.com/forum-rule...rum-rules.html

This time you are breaking:

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

PS. If the answer ( concatenated pieces of your formula with

Please Login or Register  to view this content.

in the middle) takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.