使用数组并在vba中使用以下代码查找多个字符串?

问题描述

如何在下面的代码中使用数组来查找多个字符串?

How to use array in below code to find multiple strings?

Sub Replace18()
        Dim rng As Range
        Dim rws As Long
        rws = Range("A" & Rows.Count).End(xlUp).Row - 3
        Set rng = Rows("3:3").Find(What:="quantity", LookAt:=xlWhole, MatchCase:=False)
        If Not rng Is Nothing Then
            rng.Offset(1, 0).FormulaR1C1 = "20"
            rng.Offset(1, 0).Resize(rws).FillDown
        End If
End Sub

推荐答案

另一个变体(基于 @Jeeped 答案)

another variant (based on @Jeeped answer)

Sub test()
    Dim Dic As Object, k As Variant, S$, rws&, x&, Rng As Range
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = vbTextCompare
    S = "Lorem,ipsum,dolor,amet,consectetur,adipiscing,elit,Mauris," & _
        "facilisis,rutrum,faucibus,Sed,euismod,orci,rhoncus,tincidunt,elit,eros"
    For Each k In Split(S, ",")
        If Not Dic.exists(k) Then Dic.Add k, Nothing
    Next k
    rws = Range("A" & Rows.Count).End(xlUp).Row - 3
    x = [3:3].Find("*", , , xlByColumns, , xlPrevious).Column
    For Each Rng In Range([A3], Cells(3, x))
        If Dic.exists(Rng.Value) Then
            Rng.Offset(1, 0).FormulaR1C1 = "20"
            Rng.Offset(1, 0).Resize(rws).FillDown
        End If
    Next Rng
End Sub

或

Sub test2()
    Dim Dic As Object, k As Variant, S$, rws&, x&, Rng As Range
    Set Dic = CreateObject("Scripting.Dictionary"): Dic.CompareMode = vbTextCompare
    S = "Lorem,ipsum,dolor,amet,consectetur,adipiscing,elit,Mauris," & _
        "facilisis,rutrum,faucibus,Sed,euismod,orci,rhoncus,tincidunt,elit,eros"
    For Each k In Split(S, ",")
        If Not Dic.exists(k) Then Dic.Add k, ""
    Next k
    rws = Range("A" & Rows.Count).End(xlUp).Row
    x = [3:3].Find("*", , , xlByColumns, , xlPrevious).Column
    For Each Rng In Range([A3], Cells(3, x))
        If Dic.exists(Rng.Value) Then
            Range(Cells(Rng.Row + 1, Rng.Column), Cells(rws, Rng.Column)).Value = "20"
        End If
    Next Rng
End Sub

这篇关于使用数组并在vba中使用以下代码查找多个字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！