VLookup does work in VBA

[kdshaw01]

The following Vlookup in my VBA code appears not to evaluate and I do not understand why:

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, ResourceTbl, 2, 0)

Worth Noting:
1. If I enter the formula [=VLookup(RC3, ResourceTbl, 2, False)] into a cell, the cell is populated with valid results.
2. The vba vlookup code is in a different module than the sheet where the ResourceTbl data resides.
==================================================
Here is an excerpt of the VBA code where strResourceName is valid and populated. The ResourceTbl is built and populated with data.

Dim varResourceId As Variant
Dim strResourceName As String

strResourceName = Cells(Target.Row, intResourceColumn)
varResourceId = Application.WorksheetFunction.VLookup(strResourceName, ResourceTbl, 2, 0)
==================================================
Here is the macro module that builds ResourceTbl
Sub CreateResourceTable()
' CreateResourceTable Macro
'

ActiveWorkbook.Worksheets("AMR Data").Range("D2:E65000").Copy Destination:=Sheets("AMRIdTable").Range("A1")
With ActiveWorkbook.Worksheets("AMRIdTable")
.Columns("B:B").Cut
.Range("A1").Insert shift:=xlToRight
.ListObjects.Add(xlSrcRange, Range("A:B"), , xlYes).Name = "ResourceTbl"
.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
With .ListObjects("ResourceTbl").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
======================================================
I am new to vba and excel, I'd appreciate any and all feedback
Kind Regards,

Kieran Shaw

 

[AlphaFrog]

Try this...

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").ListObjects("ResourceTbl"), 2, 0)

 

[kdshaw01]

Thank you for the advice. Unfortunately it did not work. To me, it seems that my ResourceTbl is out of scope or undefined within the confines of the sheet's macro.

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").ListObjects("ResourceTbl"), 2, 0)

It is my understanding of Excel, that Vlookup is really a sheet - cell function. Should I look at using a different VBA specific function?

Again, Thank you for your input.

Kieran Shaw

 

[AlphaFrog]

Does this work?

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").Range("A:B"), 2, 0)

 

[AlphaFrog]

Also in the code where you define ResourceTbl, you may be missing a period

ActiveWorkbook.Worksheets("AMR Data").Range("D2:E65000").Copy Destination:=Sheets("AMRIdTable").Range("A1")
With ActiveWorkbook.Worksheets("AMRIdTable")
[COLOR="Green"]'
'''''''''''''''''''''''''''''|[/COLOR]
.ListObjects.Add(xlSrcRange, [COLOR="Red"].[/COLOR]Range("A:B"), , xlYes).Name = "ResourceTbl"

 

[kdshaw01]

Thank you. The range reference made it work. I also added the missing period that you mentioned in the table creation.

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").Range("A:B"), 2, 0)

Thank you very much.

Kieran Shaw