title: Excel 2007 VBA VLookup function
body: <p>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?</p>
  
  <p>Sample Excel Sheets:
  VLookup Range Sheet</p>
  
  <pre><code>"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
  </code></pre>
  
  <p>Information Sheet</p>
  
  <pre><code>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></pre>
  
  <p>Code:</p>
  
  <pre><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" &amp; 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
  </code></pre>