excel - 连续单元格的VBA部分匹配（值/字符串）

Question

我正在为部分匹配而苦苦挣扎，这个想法是在每一行添加评论，如果与下面的行匹配，结果应该如下图所示

我的斗争是与零件匹配的部分，例如 B4 值“87032610”和 B5“付款 87032610”，我想要的结果是 C4 和 C5 列“零件匹配”中的 txt

到目前为止我的代码：

Sub testRes()
Dim i As Integer

i = 2

Do While ThisWorkbook.Worksheets("test").cells(i, 1) <> ""

If ThisWorkbook.Sheets("test").cells(i, 1) Like "*" & ThisWorkbook.Sheets("test").cells(i + 1, 1) Then
ThisWorkbook.Sheets("test").cells(i, 1).Offset(0, 1).Value = "Yes"
ThisWorkbook.Sheets("test").cells(i + 1, 1).Offset(0, 1).Value = "Yes"

Else
ThisWorkbook.Sheets("test").cells(i, 1).Offset(0, 1).Value = "No"
ThisWorkbook.Sheets("test").cells(i + 1, 1).Offset(0, 1).Value = "No"

    End If

i = i + 1
Loop


End Sub

谢谢 ：-）

标签： excelvba

Answer 1

根据发票编号检查可变付款文本

与通过 VBA 的单元格相比，循环遍历数组所需的时间更少。为了检查部分结果，只需更改Like比较方向（由周围的通配符完成*），因为发票编号代表的部分比可变支付文本（商业客户）更小。

Sub testRes()
'[0]get variant 1-based 2-dim data field array
    Dim rng As Range
    Set rng = Sheet1.Range("B2:B9")            ' << change to wanted range reference
    Dim v
    v = rng.Value
'[1]check invoice number against changing payment texts
    Dim i As Long
    Dim invoice, pmt
    For i = 1 To UBound(v) - 1 Step 2
        invoice = v(i, 1)
        pmt = v(i + 1, 1)
        If invoice = pmt Then
            v(i, 1) = "Yes"
        ElseIf pmt Like "*" & invoice & "*" Then
            v(i, 1) = "Part Match"
        Else
            v(i, 1) = "No"
        End If
        v(i + 1, 1) = v(i, 1)
    
    Next i
'[2]write results
    rng.Offset(0, 1) = v

End Sub