在VBA中处理Vlookup的公式结果-解网

问：

我在 Excel 电子表格中有一个表格，该表格从外部电子表格中提取所有数据。我创建了一个表单，该表单应将数据提取到显示与产品相关的所有详细信息的表单中。将产品编号键入到 ComboBox 中，然后使用表中的 Vlookup 进行搜索。当我键入产品编号（例如看起来像FC222555）时，在按下搜索按钮后，我收到运行时错误 1004 无法获取 worksheetfunction 类的 Vlookup 属性。我发现由于单元格中的公式，Vlookup找不到这个数字，我需要转换公式结果，以便Vlookup可以找到它。请看代码：

Private Sub CommandButton1_Click()
'variables
Dim Internal_names As String
Dim Prdd As Date
Dim Vn As String
Dim Sh1 As String
Dim Ln As Long
Dim Fx As Long
Dim Pds As String
Dim Pdd As Date
Dim Iss As String
Dim Cms As String
Dim Cmd As Date
Dim Shs As String
Dim Shd As Date
Dim Com As String
Dim x As String

'combobox value vlookup
With ThisWorkbook.Worksheets("Delivery")
Set Rng = Range("A:Y")
Prdd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 21, False)
Vn = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 15, False)
Sh1 = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 1, False)
Ln = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 2, False)
Fx = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 3, False)
Pds = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 4, False)
Pdd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 5, False)
Iss = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 6, False)
Cms = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 9, False)
Cmd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 10, False)
Shs = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 11, False)
Shd = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 12, False)
Com = Application.WorksheetFunction.VLookup(ComboBox1.Value, Rng, 14, False)

'text boxes value
dateprod.Value = Prdd
vin.Value = Vn
shift.Value = Sh1
line.Value = Ln
fixture.Value = Fx
pdishift.Value = Pds
pdidate.Value = Pdd
details.Value = Iss
cmmshift.Value = Cms
cmmdate.Value = Cmd
shipshift.Value = Shs
shipdate.Value = Shd
comments.Value = Com


End With
End Sub

我试图让变量处理公式结果，但随后我得到了一个无效的限定符错误。


Dim x As String

With ThisWorkbook.Worksheets("Delivery")
x = ComboBox1.Value
Prdd = Application.WorksheetFunction.VLookup(x.Value, Rng, 21, False)
Vn = Application.WorksheetFunction.VLookup(x.Value, Rng, 15, False)
Sh1 = Application.WorksheetFunction.VLookup(x.Value, Rng, 1, False)
Ln = Application.WorksheetFunction.VLookup(x.Value, Rng, 2, False)
Fx = Application.WorksheetFunction.VLookup(x.Value, Rng, 3, False)

你能帮我了解我需要做什么才能让它工作吗？

Excel VBA vlookup 工作表函数

如果查找表上没有与要搜索的值匹配，则会出现该错误。仅供参考，使用单个来查找匹配的行，然后直接从该行读取值会更有效。如果没有匹配，Match（）将返回一个错误值，您可以使用 IsError（）进行测试Application.Match(ComboBox1.Value, ThisWorkbook.Worksheets("Delivery").Columns("A"), 0)

答：

0赞 Tim Williams 10/26/2023 #1

如果查找表上没有与要搜索的值匹配，则会出现该错误。

仅供参考，使用单个来查找匹配的行，然后直接从该行读取值会更有效。如果没有进行匹配，将返回一个错误值，您可以使用进行测试。Application.Match()Match()IsError()

例如：

Private Sub CommandButton1_Click()
    
    Dim m As Variant, rw As Range, ws As Worksheet, v
    
    Set ws = ThisWorkbook.Worksheets("Delivery")
    
    v = ComboBox1.Value
    m = Application.Match(v, ws.Columns("A"), 0) 'try to find matched row
    
    If Not IsError(m) Then  'got a match?
        Set rw = ws.Rows(m)
        dateprod.Value = rw.Cells(21).Value
        vin.Value = rw.Cells(15).Value
        shift.Value = rw.Cells(1).Value
        Line.Value = rw.Cells(2).Value
        'etc etc
    Else
        MsgBox "No match found for '" & v & "'!", vbExclamation
    End If
   
End Sub

上一个：io.netty.handler.codec.http.multipart.HttpPostRequestEncoder$ErrorDataEncoderException：java.io.FileNotFoundException

下一个：VBA 引用公式中使用的列范围

在VBA中处理Vlookup的公式结果

Handling formula result to Vlookup in VBA

评论