From: Ayo on
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
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
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
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
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
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Conditional formating using VBA
Next: Syntax help