Excel VBA中FindNext方法无法匹配相同单元格值的问题

问题描述 #

本人刚接触Excel VBA，程序大体运行正常，但发现Do语句后，使用FindNext查找与rngchk值相同的下一个单元格时未生效。例如rngchk值为唯一的86913时，FindNext却找到84193并修改rngchk。相关代码片段如下：

Public Sub Shipped()
    
    Const WB_FULL = "Master Final SOs Testing"
    
    Dim wbFull As Workbook
    Dim wsShipped As Worksheet, wsfull As Worksheet, wsSandy As Worksheet
    Dim wsDwight As Worksheet, wsJohn As Worksheet, wsRack As Worksheet, wsBasket As Worksheet
    Dim n As Long, t0 As Single: t0 = Timer
    Dim rngFull As Range, rngSandy As Range, rngRack As Range, rngBasket As Range
    Dim rngJohn As Range, rngDwight As Range, rngchk As Range, rngShipped As Range
    Dim SO As Single, dt As Date
    Dim wsSimple As Worksheet, rngSimple As Range
    Set wbFull = Workbooks(WB_FULL)
    Set wsfull = wbFull.Worksheets("Full")
    
    With wbFull
        Set rngFull = wsfull.Cells(12, "F")
        Set wsRack = wbFull.Worksheets("Rack")
        Set wsBasket = wbFull.Worksheets("Basket")
        Set wsSandy = wbFull.Worksheets("Sandy")
        Set wsDwight = wbFull.Worksheets("Dwight")
        Set wsJohn = wbFull.Worksheets("John")
        Set wsShipped = wbFull.Worksheets("Shipped")
        Set rngRack = wsRack.Cells(6, "A")
        Set rngBasket = wsBasket.Cells(6, "A")
        Set rngDwight = wsDwight.Cells(6, "A")
        Set rngJohn = wsJohn.Cells(6, "A")
        Set rngSandy = wsSandy.Cells(6, "A")
        Set rngShipped = wsShipped.Cells(2, "A")
        Set wsSimple = wbFull.Worksheets("Simple")
        Set rngSimple = wsSimple.Cells(12, "B")
    End With

'Full
    Dim lastrow
     ' sort by SO and Line Number
    lastrow = wsfull.Cells(wsfull.Rows.Count, "B").End(xlUp).Row
    With wsfull.Sort
        With .SortFields
             .Clear
             .Add Key:=Range("F" & "11"), _
               SortOn:=xlSortOnValues, Order:=xlAscending, _
               DataOption:=xlSortNormal
             .Add Key:=Range("B" & "11"), _
               SortOn:=xlSortOnValues, Order:=xlAscending, _
               DataOption:=xlSortNormal
        End With
        .SetRange Range("A" & "11" & ":" & "L" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Full


    Do
        If rngFull.Value2 <> "" Then
            SO = rngFull.Offset(, -4).Value2
            dt = rngFull.Offset(, -1).Value2
            Dim rng1 As Range
            ' add below last row
            Set rngchk = rngFull.Offset(, -4)
            Set firstaddress = rngchk
            Set rngchk = wsfull.UsedRange.FindNext(rngchk)
            If rngchk Is Nothing Then
                If rngFull.Offset(, -5) = "S" Then
                    Call SandyS(rngSandy, SO, dt, wsSandy)
                ElseIf rngFull.Offset(, -5) = "D" Then
                    Call DwightS(rngDwight, SO, dt, wsDwight)
                ElseIf rngFull.Offset(, -5) = "J" Then
                    Call JohnS(rngJohn, SO, dt, wsJohn)
                ElseIf rngFull.Offset(, -5) = "B" Then
                    Call BasketS(rngBasket, SO, dt, wsBasket)
                ElseIf rngFull.Offset(, -5) = "R" Then
                    Call RackS(rngRack, SO, dt, wsRack)
                End If
            ElseIf (rngFull.Offset(, -4).Value2 <> rngFull.Offset(1, -4).Value2 And rngFull.Value2 <> "" And rngchk.Offset(, -1).Value2 <> firstaddress.Offset(, -1).Value2) Then
                If rngFull.Offset(, -5) = "S" Then
                    Call SandyS(rngSandy, SO, dt, wsSandy)
                ElseIf rngFull.Offset(, -5) = "D" Then
                    Call DwightS(rngDwight, SO, dt, wsDwight)
                ElseIf rngFull.Offset(, -5) = "J" Then
                    Call JohnS(rngJohn, SO, dt, wsJohn)
                ElseIf rngFull.Offset(, -5) = "B" Then
                    Call BasketS(rngBasket, SO, dt, wsBasket)
                ElseIf rngFull.Offset(, -5) = "R" Then
                    Call RackS(rngRack, SO, dt, wsRack)
                End If
            End If
        End If

问题原因 #

1. 未先执行Find方法直接调用FindNext：FindNext必须依赖之前的Find操作建立的搜索上下文，直接调用会使用Excel最近一次Find的缓存参数，导致搜索结果不符合预期。
2. 数据类型不匹配：SO被定义为Single（单精度浮点数），但SO编号是整数，浮点数可能存在精度误差，导致Find时匹配错误值。
3. Find参数未明确设置：未指定LookAt:=xlWhole等关键参数，默认可能是xlPart，会匹配包含目标值的单元格（比如86913和84193可能被部分匹配）。
4. UsedRange范围问题：wsfull.UsedRange可能包含非预期区域，建议明确搜索范围（比如SO所在的整列）。

修正方案 #

步骤1：修正数据类型 #

把SO As Single改为SO As Long，避免浮点数精度问题。

步骤2：正确使用Find+FindNext流程 #

先执行Find方法初始化搜索，再用FindNext查找下一个匹配项，同时明确Find的参数：

' 替换原Do块内的相关代码
Set rngchk = rngFull.Offset(, -4)
Set firstaddress = rngchk

' 先执行Find初始化搜索，明确参数
Set rngchk = wsfull.Columns(rngchk.Column).Find( _
    What:=rngchk.Value2, _
    After:=rngchk, _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)

' 检查是否找到下一个匹配项
If rngchk Is Nothing Or rngchk.Address = firstaddress.Address Then
    ' 没有找到其他匹配项（唯一值）
    Select Case rngFull.Offset(, -5).Value2
        Case "S": Call SandyS(rngSandy, SO, dt, wsSandy)
        Case "D": Call DwightS(rngDwight, SO, dt, wsDwight)
        Case "J": Call JohnS(rngJohn, SO, dt, wsJohn)
        Case "B": Call BasketS(rngBasket, SO, dt, wsBasket)
        Case "R": Call RackS(rngRack, SO, dt, wsRack)
    End Select
Else
    ' 处理找到匹配项的逻辑，注意循环终止条件
    Do While rngchk.Address <> firstaddress.Address
        ' 这里可以添加对找到的rngchk的处理逻辑
        ' ...
        Set rngchk = wsfull.Columns(rngchk.Column).FindNext(rngchk)
    Loop
    ' 原有的判断逻辑
    If (rngFull.Offset(, -4).Value2 <> rngFull.Offset(1, -4).Value2 And rngFull.Value2 <> "" And rngchk.Offset(, -1).Value2 <> firstaddress.Offset(, -1).Value2) Then
        Select Case rngFull.Offset(, -5).Value2
            Case "S": Call SandyS(rngSandy, SO, dt, wsSandy)
            Case "D": Call DwightS(rngDwight, SO, dt, wsDwight)
            Case "J": Call JohnS(rngJohn, SO, dt, wsJohn)
            Case "B": Call BasketS(rngBasket, SO, dt, wsBasket)
            Case "R": Call RackS(rngRack, SO, dt, wsRack)
        End Select
    End If
End If

补充说明 #

• 使用Columns(rngchk.Column)限定搜索范围为SO所在的整列，比UsedRange更精准。
• LookAt:=xlWhole确保匹配整个单元格的值，避免部分匹配导致的错误。
• 检查rngchk.Address = firstaddress.Address可以判断是否回到了起始单元格，避免无限循环。

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