Excel 2007 VBA VLookup function

Question

I have put together code to use vlookup using vba in excel. However, I am receiving an error indicating "Unable to get the VLOOKUP property of the WorkSheet Function class". "CustomerNumberList" is a two column range. I believe my issue is using the VLookup function. I am using "Offset" as an indicator of where I want VLookup to start looking in the sheet, in this case .Find "cust_num"(y+1, 0). How do I indicate what column I want VLookup to enter company name? Currently my module has a Select Case function that works in getting the company names in each cell, but I am dealing with thousands of lines so it can take 20 minutes to run. I am hoping this way is faster. Would someone be able to assist?

Sample Excel Sheets: VLookup Range Sheet

"CustomerNumberList"
Cust_Num    Company Name
10001       CompanyX
10002       CompanyX
10003       CompanyX
10004       CompanyX
10005       CompanyX
10006       CompanyX
10007       CompanyX
10008       CompanyX
10009       CompanyX
10010       CompanyY
10011       CompanyY
10012       CompanyY
10013       CompanyY
10014       CompanyY
10015       CompanyY
10016       CompanyY
10017       CompanyY

Information Sheet

Sheet1                      
OrderID Cust_Num    Company Name    Customer Name   ShipLocation    Cost    Price
1       10001         VLookupHere    Rand                Dallas    $1.00    $2.00
2       10002             "          Rand                Chicago    $2.00   $3.00
3       10003              "         Rand               Florida    $1.00    $2.00
4       10004              "          Wel                California $1.33   $2.33
5       10005                         Wel                Dallas    $1.33    $2.33
6       10006                         Wel                Chicago    $1.33   $2.33
7       10007                        Sead                Florida    $1.33   $2.33
8       10008                        Sead                California $1.33   $2.33
9       10009                        Sead                Dallas    $1.33    $2.33
10      10010                        Sead                Chicago    $1.33   $2.33
11      10011                        Sead                 Florida   $1.33   $2.33
12      10012                        Sead                California $1.33   $2.33
13      10013                        Campe                Dallas    $1.33   $2.33
14      10014                        Campe                Chicago   $1.33   $2.33
15      10015                        Campe                Florida   $1.33   $2.33
16      10016                        Campe               California $1.33   $2.33
17      10017                        Campe                Dallas    $1.33   $2.33

Code:

Dim Nu As Range
Dim cmpny As Range
Dim v As Integer
Dim y As Integer
Dim ws As Worksheet
Dim Offset As Range
Dim result As String
'
'
'
'Insert company name into each row based on cust_num
Set Nu = Sheets("CustomerNumberList").Range("A1") 'set Nu = cust_name region
Set cmpny = Sheets("CustomerNumberList").Range("A1").CurrentRegion 'set cmpny = cell range
For Each ws In Sheets
    If ws.Name Like "*Sheet*" Then
        v = ws.Range("A" & Rows.Count).End(xlUp).Row 'set v = number of rows
        Set Nm = ws.Rows(1).Find("cust_num", LookAt:=xlPart)
        For y = 0 To v
         Set Offset = Nm.Offset(1 + y, 0)
         result = Application.WorksheetFunction.VLookup(Nu.Value, cmpny, 2, Offset, False)      **'ERROR HERE**
        Next
    End If
Next
End Sub

Too many parameters in your call to vlookup. Likely 2 is the extra one... — Tim Williams
@TimWilliams, my initial code excluded 2 and I was getting the same error. My thinking is that the "Offset" did not follow the vlookup function procedure since it refers to the information sheet rather than the vlookup range sheet. — Zachary Smith
The third parameter needs to be a number, so what the value of Offset? — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2013-07-03T05:01:15

Sub Update()

Dim rngNums As Range
Dim cmpny As Range
Dim v As Long
Dim ws As Worksheet
Dim result As Variant
Dim c As Range, nm As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    On Error GoTo haveError

    'lookup range
    Set cmpny = Sheets("CustomerNumberList").Range("A1").CurrentRegion
    For Each ws In Sheets
        If ws.Name Like "*Sheet*" Then

            v = ws.Range("A" & Rows.Count).End(xlUp).Row 'set v = number of rows

            Set nm = ws.Rows(1).Find("cust_num", LookAt:=xlPart)

            If Not nm Is Nothing Then
                Set rngNums = ws.Range(nm.Offset(1, 0), ws.Cells(v, nm.Column))
                For Each c In rngNums.Cells
                    If c.Value <> "" Then
                        'Next line drops the WorksheetFunction to avoid raising error
                        '   if the value is not found - instead, test the return value
                        result = Application.VLookup(c.Value, cmpny, 2, False)
                        c.Offset(0, 1).Value = IIf(IsError(result), "???", result)
                    End If
                Next c
            Else
                Debug.Print "No 'cust_num' in row 1 on sheet '" _
                             & ws.Name & "'"
            End If

        End If
    Next ws

haveError:
    If Err.Number <> 0 Then MsgBox Err.Description, vbExclamation
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Excel 2007 VBA VLookup function

1 Answers