VLookup does work in VBA

kdshaw01

New Member
Joined
Oct 17, 2011
Messages
11
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
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try this...

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").ListObjects("ResourceTbl"), 2, 0)
 
Upvote 0
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
 
Upvote 0
Does this work?

varResourceId = Application.WorksheetFunction.VLookup(strResourceName, Worksheets("AMRIdTable").Range("A:B"), 2, 0)
 
Upvote 0
Also in the code where you define ResourceTbl, you may be missing a period

Code:
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,202,988
Messages
6,052,944
Members
444,619
Latest member
Pawar537

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top