Excel 2007 VBA VLookup 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17438565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2007 VBA VLookup function
提问by Zachary Smith
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?
我已经在excel中使用vba编写了使用vlookup的代码。但是，我收到一条错误消息，指出“无法获取 WorkSheet 函数类的 VLOOKUP 属性”。“CustomerNumberList”是一个两列范围。我相信我的问题是使用 VLookup 函数。我使用“偏移量”作为我希望 VLookup 在工作表中开始查找的位置的指示符，在本例中为 .Find“cust_num”(y+1, 0)。如何指示我希望 VLookup 输入公司名称的列？目前，我的模块有一个 Select Case 函数，可用于获取每个单元格中的公司名称，但我正在处理数千行，因此运行可能需要 20 分钟。我希望这种方式更快。有人可以提供帮助吗？
Sample Excel Sheets: VLookup Range Sheet
示例 Excel 表：VLookup 范围表
"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 .00 .00
2 10002 " Rand Chicago .00 .00
3 10003 " Rand Florida .00 .00
4 10004 " Wel California .33 .33
5 10005 Wel Dallas .33 .33
6 10006 Wel Chicago .33 .33
7 10007 Sead Florida .33 .33
8 10008 Sead California .33 .33
9 10009 Sead Dallas .33 .33
10 10010 Sead Chicago .33 .33
11 10011 Sead Florida .33 .33
12 10012 Sead California .33 .33
13 10013 Campe Dallas .33 .33
14 10014 Campe Chicago .33 .33
15 10015 Campe Florida .33 .33
16 10016 Campe California .33 .33
17 10017 Campe Dallas .33 .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
采纳答案by Tim Williams
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
