如何避免Split数组添加前导零？VBA查找匹配失败问题

问题描述 #

我有一串条码字符串（如817513015311, 787734332439），使用Split拆分后，通过以下代码在另一工作表中查找PCodeToChk：

Set UpdateRow = Sheets("MASTER - Supplier Price File").Range("f2:f" & LastRow_MSF).Find(PCodeToChk)

目标工作表中的值无前置零，但Split后的数据被添加了前导零，导致查找无法匹配。怀疑空格可能是问题所在，同时想了解其他可靠的查找方法。

完整VBA代码如下：

Option Explicit
Sub ReverseSearch()
Dim WS_MSF As Worksheet
Dim WS_ADF As Worksheet
Dim WS_RES As Worksheet
Dim TBL_MSF  As ListObject
Dim TBL_ADF  As ListObject
Set WS_MSF = Sheets("MASTER - Supplier Price File")
Set WS_ADF = Sheets("IMPORT - Amazon Data")
Set WS_RES = Sheets("Sheet2")

Set TBL_MSF = WS_MSF.ListObjects("MSPF_Table")
Set TBL_ADF = WS_ADF.ListObjects("IMPORT_Table")


Dim c As Long
Dim LastRow_MSF As Long
Dim LastRow_ADF As Long
Dim EANToChk As String
Dim EAN_Split() As String
Dim PCodeToChk As String

Dim PCode_Split() As String

Dim I As Long
Dim UpdateRow As Range
Dim nextUpdateRow As Long
Dim foundrow As Long
Dim MatchedEAN As String
Dim Supplier As String
Dim Item As String
Dim ItemDesc As String
Dim NettUnitCost As Double
Dim Match As String
Dim Asin As String

'
'Get Last Row of Master Supplier File & Amazon Data File
'
LastRow_MSF = TBL_MSF.Range.Rows(TBL_MSF.Range.Rows.Count).Row
LastRow_ADF = TBL_ADF.Range.Rows(TBL_ADF.Range.Rows.Count).Row

nextUpdateRow = 1
For c = 2 To LastRow_ADF
Asin = WS_ADF.Range("d" & c)
EAN_Split = Split(WS_ADF.Range("a" & c), ",")
PCode_Split = Split(WS_ADF.Range("b" & c), ",")
WS_MSF.Activate

Debug.Print Asin
'
'Check EAN field
'
For I = LBound(EAN_Split()) To UBound(EAN_Split())
Match = ""
EANToChk = EAN_Split(I)


Set UpdateRow = Sheets("MASTER - Supplier Price File").Range("f2:f" & LastRow_MSF).Find(EANToChk)
    
             If Not UpdateRow Is Nothing Then
                    Match = "Match"
                    foundrow = UpdateRow.Row
                    MatchedEAN = WS_MSF.Range("f" & foundrow).Value
                    Supplier = WS_MSF.Range("c" & foundrow).Value
                    Item = WS_MSF.Range("g" & foundrow).Value
                    ItemDesc = WS_MSF.Range("h" & foundrow).Value
                    NettUnitCost = WS_MSF.Range("m" & foundrow).Value
                    nextUpdateRow = nextUpdateRow + 1
                    WS_RES.Range("a" & nextUpdateRow) = MatchedEAN
                    WS_RES.Range("b" & nextUpdateRow) = Supplier
                    WS_RES.Range("c" & nextUpdateRow) = NettUnitCost
                    WS_RES.Range("d" & nextUpdateRow) = Item
                    WS_RES.Range("e" & nextUpdateRow) = ItemDesc
                    WS_RES.Range("f" & nextUpdateRow) = "EAN"
                    If Match = "Match" Then Exit For
              End If
Next I
'
'Check Part code field
'
If Match = "" Then
For I = LBound(PCode_Split()) To UBound(PCode_Split())
PCodeToChk = PCode_Split(I)

Debug.Print PCodeToChk

Set UpdateRow = Sheets("MASTER - Supplier Price File").Range("f2:f" & LastRow_MSF).Find(PCodeToChk)
    
             If Not UpdateRow Is Nothing Then
                    Match = "Match"
                    foundrow = UpdateRow.Row
                    MatchedEAN = WS_MSF.Range("f" & foundrow).Value
                    Supplier = WS_MSF.Range("c" & foundrow).Value
                    Item = WS_MSF.Range("g" & foundrow).Value
                    ItemDesc = WS_MSF.Range("h" & foundrow).Value
                    NettUnitCost = WS_MSF.Range("m" & foundrow).Value
                    nextUpdateRow = nextUpdateRow + 1
                    WS_RES.Range("a" & nextUpdateRow) = MatchedEAN
                    WS_RES.Range("b" & nextUpdateRow) = Supplier
                    WS_RES.Range("c" & nextUpdateRow) = NettUnitCost
                    WS_RES.Range("d" & nextUpdateRow) = Item
                    WS_RES.Range("e" & nextUpdateRow) = ItemDesc
                    WS_RES.Range("f" & nextUpdateRow) = "PartCode"
                    
                    If Match = "Match" Then Exit For
              End If
              If Match = "" Then
              WS_RES.Range("a" & nextUpdateRow) = MatchedEAN
              WS_RES.Range("g" & nextUpdateRow) = Asin
              WS_RES.Range("f" & nextUpdateRow) = "No Match"
              WS_RES.Range("h" & nextUpdateRow) = WS_ADF.Range("a" & c)
              WS_RES.Range("i" & nextUpdateRow) = WS_ADF.Range("b" & c)
              End If
Next I
End If



Next c
End Sub

相关截图说明 #

• 返回值截图：显示Split拆分后，待查找的条码字符串被添加了前导零
• 待查找值截图：显示目标工作表中存储的原始条码值，无前置零

解决方案 #

1. 清理拆分后的数据 #

Split拆分后的元素常带有前后空格，且Excel可能自动为数字补前导零，需先清洗数据：

纯数字条码处理

' 处理EANToChk
EANToChk = Trim(EAN_Split(I)) ' 去除前后空格
EANToChk = CStr(CLng(EANToChk)) ' 转数字再转回字符串，自动去除前导零

' 处理PCodeToChk同理
PCodeToChk = Trim(PCode_Split(I))
PCodeToChk = CStr(CLng(PCodeToChk))

含字母的条码处理（用正则表达式）

需先添加引用：工具 -> 引用 -> Microsoft VBScript Regular Expressions 5.5

Dim regEx As New RegExp
regEx.Pattern = "^0+" ' 匹配开头的所有前导零
PCodeToChk = regEx.Replace(Trim(PCode_Split(I)), "")

2. 优化Find方法参数 #

默认Find方法易受格式影响，指定参数确保精准匹配：

Set UpdateRow = Sheets("MASTER - Supplier Price File").Range("f2:f" & LastRow_MSF).Find( _
    What:=PCodeToChk, _
    LookIn:=xlValues, _ ' 查找单元格实际值而非格式
    LookAt:=xlWhole, _ ' 完全匹配，避免部分匹配
    MatchCase:=False _
)

3. 替代方案：使用Match函数 #

Match函数在精准匹配上更稳定，适合数据查找场景：

Dim matchRow As Variant
' 从F2开始查找，匹配类型0表示完全匹配
matchRow = Application.Match(PCodeToChk, Sheets("MASTER - Supplier Price File").Range("f2:f" & LastRow_MSF), 0)
If Not IsError(matchRow) Then
    ' 因为从F2开始，所以实际行号是matchRow+1
    Set UpdateRow = Sheets("MASTER - Supplier Price File").Range("f" & (matchRow + 1))
    ' 后续匹配成功的处理逻辑...
End If

4. 统一单元格格式 #

确保目标工作表F列设置为文本格式，避免Excel自动转换数字格式，保证查找值与目标值数据类型一致。

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