如何解决搜索非精确匹配时的VBA类型不匹配运行时错误

一、类型不匹配错误修复 #

错误发生在chkshotnumber = icsws.Cells(Rows.Count, 8).End(xlUp)行，核心原因：

1. 直接将单元格对象赋值给Long类型变量，未指定读取单元格的.Value属性
2. chkshotnumber定义为Long，但TextBox6可能输入文本内容，类型不兼容

修复方案：

• 将chkshotnumber的类型改为String，适配文本/数字混合的搜索场景
• 正确获取单元格值：chkshotnumber = icsws.Cells(Rows.Count, 8).End(xlUp).Value

二、非精确匹配搜索实现 #

原代码默认是精确匹配，要实现模糊搜索需调整Find方法参数：

• 设置LookAt:=xlPart，匹配单元格内容的任意部分
• 补充LookIn:=xlValues确保搜索单元格值而非公式
• 添加.FindNext(rng)逻辑，遍历所有匹配项，避免死循环

三、修改后的完整代码 #

Private Sub CommandButton2_Click()

    Dim icsws As Worksheet
    Dim rsdws As Worksheet
    Dim chkshotnumber As String  ' 修改为String类型，适配文本内容
    Dim rnum As Long
    Dim icsrnum As Long
    Dim firstaddress As String
    Dim chkshot As Long
    Dim rng As Range
    
    Set icsws = Sheets("Inverse Check Shots")
    Set rsdws = Sheets("Data")
    
    chkshot = icsws.Cells(Rows.Count, 3).End(xlUp).Offset(1).Row
               
    ' 将文本框内容写入工作表
    icsws.Cells(chkshot, 3).Value = TextBox1.Text
    icsws.Cells(chkshot, 4).Value = TextBox2.Text
    icsws.Cells(chkshot, 5).Value = TextBox3.Text
    icsws.Cells(chkshot, 6).Value = TextBox4.Text
    icsws.Cells(chkshot, 7).Value = TextBox5.Text
    icsws.Cells(chkshot, 8).Value = TextBox6.Text
    
    ' 修复：获取单元格的值而非对象，类型改为String
    chkshotnumber = icsws.Cells(Rows.Count, 8).End(xlUp).Value
    
    With Sheets("Data").Columns("A:A")
        ' 非精确匹配：设置LookAt:=xlPart，搜索值而非公式
        Set rng = .Find(What:=chkshotnumber, LookIn:=xlValues, LookAt:=xlPart)
        
        If rng Is Nothing Then
            MsgBox "No Control Points Found Matching"
            GoTo Ende
        Else
            firstaddress = rng.Address
            Do
                rnum = rng.Row
                icsrnum = icsws.Cells(Rows.Count, 8).End(xlUp).Offset(1).Row
                
                ' 复制匹配行的数据
                icsws.Cells(icsrnum, 3).Value = rsdws.Cells(rnum, 1).Value
                icsws.Cells(icsrnum, 4).Value = rsdws.Cells(rnum, 2).Value
                icsws.Cells(icsrnum, 5).Value = rsdws.Cells(rnum, 3).Value
                icsws.Cells(icsrnum, 6).Value = rsdws.Cells(rnum, 4).Value
                icsws.Cells(icsrnum, 7).Value = rsdws.Cells(rnum, 5).Value
                icsws.Cells(icsrnum, 8).Value = rsdws.Cells(rnum, 11).Value
                
                ' 查找下一个匹配项
                Set rng = .FindNext(rng)
            ' 循环直到回到第一个匹配地址
            Loop While Not rng Is Nothing And rng.Address <> firstaddress
        End If
    End With
Ende:
    MsgBox ("Check Shot has been added to Inverse Check Shots Tab")

End Sub

关键修改说明 #

• 变量类型调整：将chkshotnumber改为String，避免数字/文本类型冲突
• 单元格值读取：补充.Value属性，正确获取单元格内容而非对象
• 模糊搜索配置：Find方法添加LookAt:=xlPart参数，实现非精确匹配
• 循环逻辑优化：添加.FindNext(rng)并修正循环条件，确保遍历所有匹配项，避免死循环

内容的提问来源于stack exchange，提问作者Joseph Porter