VLOOKUP syntax

  • Thread starter Thread starter JC
  • Start date Start date
J

JC

I am trying to use a VLOOKUP function within a macro. The
code I am currently attempting to use is:
VLOOKUP(RC3,'LMon & " CF"'!R8C3:R260C27,'LMon & " CF"'!R[4]
C[11],0)

where LMon is a defined variable that holds name of the
previous month. For example "Dec CF" is one of the sheets
in my workbook.

With the syntax above I am getting a Compile Error. Can
someone tell me how this shuld be correctly written.

TIA,
JC
 
VLOOKUP(RC3,'LMon & " CF"'!R8C3:R260C27,'LMon & " CF"'!R[4]C[11],0)
Click to expand...

1. In order to use the VLOOKUP function inside a macro, you have to get
it out of the WorksheetFunction object.
2. In order to use variable substitution you need to first build the
string and then use the Range VBA function.

The & operator only concatenates two strings together. The string you
need consists of:
- a literal apostrophe
- the contents of the variable Lmon
- a literal space, CF, apostrophe, exclamation point, cell address

Literals must be enclosed in quotation marks.

WorksheetFunction.VLOOKUP(RC3, _
Range("'" & Lmon & " CF'!R8C3:R260C27"), _
Range("'" & Lmon & " CF'!R[4]C[11]"), _
0 )
 
I'm kind of confused what rc3 refers to. In my example, I used the activesheet.

(You used LMon & " CF" explicitly in the other portions, but nothing with RC3.
(same row, column C of the active sheet???).

Sometimes it's easier building the pieces of the formula so you can get each
piece correct:

Option Explicit
Sub testme()

Dim myVar As Variant
Dim Lmon As String

Dim LookUpRng As Range
Dim LookUpColumn As Long
Dim whatToLookup As Variant

'VLOOKUP(RC3,'LMon & " CF"'!R8C3:R260C27,'LMon & " CF"'!R[4]C[11],0)

Lmon = "test" 'whatever it would be.

With Worksheets(Lmon & " cf")
Set LookUpRng = .Range(.Cells(8, 3), .Cells(260, 27))
'this should be numeric!
LookUpColumn = CLng(.Cells(4, 11).Value)
End With

With ActiveSheet
whatToLookup = .Cells(ActiveCell.Row, 3).Value
End With


myVar = Application.VLookup(whatToLookup, LookUpRng, LookUpColumn, 0)

If IsError(myVar) Then
MsgBox "I'd get #n/a in a worksheet"
Else
MsgBox myVar & " Is what I'd return"
End If

End Sub


I am trying to use a VLOOKUP function within a macro. The
code I am currently attempting to use is:
VLOOKUP(RC3,'LMon & " CF"'!R8C3:R260C27,'LMon & " CF"'!R[4]
C[11],0)

where LMon is a defined variable that holds name of the
previous month. For example "Dec CF" is one of the sheets
in my workbook.

With the syntax above I am getting a Compile Error. Can
someone tell me how this shuld be correctly written.

TIA,
JC
Click to expand...
 
Back
Top