时间戳

首页 > 解决方案 > VBA 查找不匹配

excel - VBA 查找不匹配

问题描述

我回顾了以前的问题，但找不到答案。我在 VBA 中使用 VLOOKUP 来使用基于选择的数据填充用户表单。我继续在某些条目上出现不匹配，而其他条目则有效，现在没有。这是代码：

Private Sub jobRefCbo_Change()
Application.ScreenUpdating = False

Call Lists_sort ' This just sorts a list of the 'Active' data so the user only sees the active jobs in the combo box. 



    Me.nameTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 2, False)
    'Me.acNoTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 2, False)
    Me.jobDesc2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 3, False)
    'Me.date2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 4, False)
    Me.month2Txt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 5, False)
    Me.timeOnJobTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 6, False)
    Me.StatusTxt.Value = Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 7, False)
    Me.startTime2Txt.Value = Format(CDate(Application.WorksheetFunction.VLookup(CDbl(Me.jobRefCbo.Value), Worksheets("Lists ").Range("I3:P21"), 8, False)), "hh:mm:ss AM/PM")

    Application.ScreenUpdating = True

End Sub

一般而言，任何这样做的建议都非常感谢。

标签： excelvba

解决方案

修复建议：

Private Sub jobRefCbo_Change()

    Dim m, v, rngSearch As Range

    Application.ScreenUpdating = False

    Lists_sort 'use of Call is deprecated

    v = Trim(Me.jobRefCbo.Value)

    If IsNumeric(v) Then
        Set rngSearch = Worksheets("Lists ").Range("I3:P21")
        'use Match to find a hit in the first column...
        m = Application.Match(CDbl(v), rngSearch.Columns(1), 0)
        If Not IsError(m) Then
            'got a hit so return the values
            With rngSearch.Rows(m)
                Me.nameTxt.Value = .Cells(2).Value
                Me.jobDesc2Txt.Value = .Cells(3).Value
                'etc etc
            End With
        End If
    Else
        MsgBox "Lookup value must be numeric", vbExclamation
    End If

    Application.ScreenUpdating = True

End Sub

推荐阅读