Solved: Accessing The Reference Cell Contained In The Excel VLookUp() Function From VBA !!

[lifetree]

I am trying to access the Excel VLoopUp() function from within a VBA procedure. Assume that the active cell is A3 and I am inserting the formula to cell D3, and the look up value is contained in cell A3. I did a search on this that yielded some possible code alternatives, among them are ...

[vba]
ActiveCell.Offset(0, 3).Formula = "=VLOOKUP(RC[-3],'SvcLineScheme-THA-Acct-GHA'!$B$2:$F$749,5,FALSE)"

ActiveCell.Offset(0, 3).Formula = "=VLOOKUP(A" & cell.Row &",'SvcLineScheme-THA-Acct-GHA'!$B$2:$F$749,5,FALSE)"

ActiveCell.Offset(0, 3).Formula = "=VLOOKUP(ActiveCell.Value,'SvcLineScheme-THA-Acct-GHA'!$B$2:$F$749,5,FALSE)"[/vba]

However, these alternatives are not working for me. Also, I wrote a procedure, as follows ...

[vba]Sub LookUpVal()

Dim strCol As String
Dim lngCellNo As Long
Dim strLookUp As String

strCol = A
lngCellNo = 2
lngCellValue = CStr(lngCellNo)
strLookUp = strCol + lngCellValue

Range("A2").Select

Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, 3).Formula = _
"=VLOOKUP(strLookUp,'SvcLineScheme-THA-Acct-GHA'!$B$2:$F$749,5,FALSE)"
lngCellNo = lngCellNo + 1
lngCellValue = CStr(lngCellNo)
strLookUp = strCol + lngCellValue
ActiveCell.Offset(1, 0).Select
Loop
End Sub[/vba]

None of these alternatives seems to be working in order to cause the VLookUp() function to grab the value contained in cell A3 ... any advice or suggestions on this would be appreciated.

Thanks.

[Bob Phillips]

[vba]

Sub LookUpVal()

Dim strCol As String
Dim lngCellNo As Long
Dim strLookUp As String

strCol = A
lngCellNo = 2
lngCellValue = CStr(lngCellNo)
strLookUp = strCol + lngCellValue

Range("A2").Select

Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, 3).Formula = _
"=VLOOKUP(" & strLookUp & ",'SvcLineScheme-THA-Acct-GHA'!$B$2:$F$749,5,FALSE)"
lngCellNo = lngCellNo + 1
lngCellValue = CStr(lngCellNo)
strLookUp = strCol + lngCellValue
ActiveCell.Offset(1, 0).Select
Loop
End Sub [/vba]

[lifetree]

XLD -- Thank you ... your input on the syntax worked, it's exactly what I needed !!