VLookup in VBA Help Needed
in [Programming]
|
Prev: Conditional formating using VBA
Next: Syntax help
From: Ayo on 8 Apr 2010 15:19 I am getting an "Unable to get VLookup property of WorksheetFunction class" error on line "c.Offset(0, -1) =", even though VendorTable Referto:=AAV_Table!$B$2:$C$1260 Private Sub cmdUpdate_Click() Dim c As Range Dim lRow As Long Worksheets("MarketList").Activate lRow = ActiveSheet.UsedRange.Rows.Count For Each c In Worksheets("MarketList").Range("C2:C" & lRow).Cells c.Offset(0, -1) = Application.WorksheetFunction.VLookup(c, VendorTable,2,False) Next c End Sub VendorTable defined here: Worksheets("AAV_Table").Activate BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count ThisWorkbook.Names.Add Name:="VendorTable", _ RefersTo:=Worksheets("AAV_Table").Range("B2:C" & BOReport_lastRow)
From: OssieMac on 8 Apr 2010 15:53 Hi, VendorTable is a named range and not a VBA variable so you use it like the following c.Offset(0, -1) = Application.WorksheetFunction.VLookup(c, Range("VendorTable"), 2, False) Alternatively you can declare VendorTable as a range variable and use it like your example but it needs to be done just prior to the VLooup. Dim c As Range Dim lRow As Long Dim BOReport_lastRow Dim VendorTable As Range Worksheets("MarketList").Activate lRow = ActiveSheet.UsedRange.Rows.Count With Worksheets("AAV_Table") BOReport_lastRow = .UsedRange.Rows.Count Set VendorTable = .Range("B2:C" & BOReport_lastRow) End With For Each c In Worksheets("MarketList").Range("C2:C" & lRow).Cells c.Offset(0, -1) = Application.WorksheetFunction.VLookup(c, VendorTable, 2, False) Next c -- Regards, OssieMac
From: OssieMac on 8 Apr 2010 16:02 Also there is no need to activate/select worksheets or ranges. The following code Worksheets("MarketList").Activate lRow = ActiveSheet.UsedRange.Rows.Count can be replaced with this With Worksheets("MarketList") lRow = .UsedRange.Rows.Count End With or with this lRow = Worksheets("MarketList").UsedRange.Rows.Count -- Regards, OssieMac
From: Ayo on 8 Apr 2010 16:06 Thanks Mac. Now how do I account for the occassion when Application.WorksheetFunction.VLookup(c, VendorTable, 2,False) IsNA?. How do I write that in code. "OssieMac" wrote: > Hi, > > VendorTable is a named range and not a VBA variable so you use it like the > following > > c.Offset(0, -1) = Application.WorksheetFunction.VLookup(c, > Range("VendorTable"), 2, False) > > Alternatively you can declare VendorTable as a range variable and use it > like your example but it needs to be done just prior to the VLooup. > > Dim c As Range > Dim lRow As Long > Dim BOReport_lastRow > Dim VendorTable As Range > > Worksheets("MarketList").Activate > lRow = ActiveSheet.UsedRange.Rows.Count > > With Worksheets("AAV_Table") > BOReport_lastRow = .UsedRange.Rows.Count > Set VendorTable = .Range("B2:C" & BOReport_lastRow) > End With > > For Each c In Worksheets("MarketList").Range("C2:C" & lRow).Cells > > c.Offset(0, -1) = Application.WorksheetFunction.VLookup(c, VendorTable, 2, > False) > > Next c > > > > -- > Regards, > > OssieMac > >
From: Ayo on 8 Apr 2010 16:16
Thanks Mac. Now how do I account for the occassion when Application.WorksheetFunction.VLookup(c, VendorTable, 2,False) IsNA?. How do I write that in code. "OssieMac" wrote: > Also there is no need to activate/select worksheets or ranges. The following > code > > Worksheets("MarketList").Activate > lRow = ActiveSheet.UsedRange.Rows.Count > > can be replaced with this > > With Worksheets("MarketList") > lRow = .UsedRange.Rows.Count > End With > > or with this > lRow = Worksheets("MarketList").UsedRange.Rows.Count > > -- > Regards, > > OssieMac > > |