我在Excel的Vlookup功能方面需要一些帮助。我有2个工作表。第一个是公司代码,第二个我需要根据公司代码找到值。相关代码是:
Dim Compcode, AUC, OB As String
Dim WS1, WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
Set WS2 = ThisWorkbook.Worksheets("Data")
Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2
AUC = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 2, False)
OB = Application.WorksheetFunction.VLookup(Compcode, WS2.Range("A2:C30"), 3, False)
我尝试修改每个变量和Vlookup函数本身的昏暗,但是我总是会遇到相同的错误消息:
运行时错误'438':对象不支持此属性或方法
WorksheetFunction.Vlookup
对输入非常挥发,并且可以在遇到的情况下轻松丢弃运行时错误。
您的问题可能是许多事情之一,但我建议的解决方案就是仅使用Find
:
Sub test()
Dim Compcode As Variant, AUC As Variant, OB As Variant
Dim WS1 As Worksheet, WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
Set WS2 = ThisWorkbook.Worksheets("Data")
Compcode = WS1.Cells(2, 1).Value 'Company code WS1 A2
Dim rng As Range
Set rng = WS2.Range("A2:C30").Find(Compcode, , , xlWhole)
If Not rng Is Nothing Then
AUC = rng.Offset(0, 1).Value
OB = rng.Offset(0, 2).Value
End If
End Sub
问题在许多地方都可以。无论如何,Vookup在数字和字符串上有点挑剔,因此Compcode
应该是Variant
这样的事情将奏效:
Option Explicit
Public Sub TestMe()
Dim Compcode As Variant
Dim AUC As String
Dim OB As String
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = ThisWorkbook.Worksheets("Main Sheet")
Set WS2 = ThisWorkbook.Worksheets("Data")
Compcode = WS1.Cells(2, 1).Value
Dim rngRange As Range
Set rngRange = WS2.Range("A2:C30")
AUC = Application.WorksheetFunction.VLookup(Compcode, rngRange, 2, False)
OB = Application.WorksheetFunction.VLookup(Compcode, rngRange, 3, False)
End Sub
您应该明确声明每个变量,这不是c++/c
用逗号声明它们,并希望Vbeditor可以理解。
我也将公式更改为范围。