Excel VBA如何对VLookup返回的Soneri表D列数值进行自增？

错误原因 #

• 核心问题是单元格内容匹配判断没有处理不可见字符：Excel单元格经常会包含首尾空格、不可见换行符等，你直接用entry = entry.Offset(-1)判断，很容易因为肉眼不可见的字符导致判断失败，i被反复重置为0，最终只有前两行能正确触发自增逻辑。
• 现有后缀截取逻辑只支持1位数字后缀：如果同分组的条目超过9个，后缀变成两位数后，Left(b, Len(b) - 1)和Right(b, 1)的逻辑会直接出错。
• 没有添加Vlookup匹配失败的异常处理：如果I列出现mapping表中不存在的内容，代码会直接抛出运行时错误中断执行。

修正后代码 #

Sub ButtonClick()
    Dim soneriWs As Worksheet, mappingWs As Worksheet
    Dim sonerilastrow As Long, mappinglastrow As Long, i As Long
    Dim datarange As Range, assetrange As Range, b As Range
    Dim entry As Range
    Dim lookupResult As Variant, prefix As String, suffixNum As Long
    
    Set soneriWs = ThisWorkbook.Worksheets("Soneri")
    Set mappingWs = ThisWorkbook.Worksheets("Mapping")
    
    sonerilastrow = soneriWs.Range("I" & soneriWs.Rows.Count).End(xlUp).Row
    mappinglastrow = mappingWs.Range("A" & mappingWs.Rows.Count).End(xlUp).Row
    
    Set datarange = mappingWs.Range("A2:B" & mappinglastrow)
    Set assetrange = soneriWs.Range("I2:I" & sonerilastrow)
    
    i = 0
    For Each entry In assetrange
        Set b = entry.Offset(0, -5)
        ' 处理Vlookup匹配失败的情况
        lookupResult = Application.VLookup(entry.Value, datarange, 2, False)
        If IsError(lookupResult) Then
            b.Value = "未匹配"
            i = 0
            GoTo NextLoop
        End If
        
        ' 用Trim处理首尾空格，避免不可见字符导致判断失败
        If Trim(entry.Value) = Trim(entry.Offset(-1).Value) Then
            i = i + 1
            ' 通用逻辑：分离编码的前缀和数字后缀，支持任意长度的后缀
            Call SplitCode(lookupResult, prefix, suffixNum)
            b.Value = prefix & (suffixNum + i)
        Else
            i = 0
            b.Value = lookupResult
        End If
NextLoop:
    Next entry
End Sub

' 辅助函数：拆分编码的前缀和最后的数字后缀
Sub SplitCode(code As String, ByRef prefix As String, ByRef suffixNum As Long)
    Dim j As Long
    For j = Len(code) To 1 Step -1
        If Not IsNumeric(Mid(code, j, 1)) Then
            Exit For
        End If
    Next j
    prefix = Left(code, j)
    suffixNum = Val(Right(code, Len(code) - j))
End Sub

优化说明 #

• 新增Trim函数处理单元格内容，避免首尾空格导致的相等判断失败。
• 新增通用的编码拆分辅助函数，支持任意长度的数字后缀，哪怕同分组条目超过9个也能正确自增。
• 新增Vlookup匹配失败的处理逻辑，避免代码异常中断。

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