Excel VBA -使用数组查找和替换字符串

Question

我使用数组来查找某些字符串并将它们替换为一个更标准的值：

Dim Keywords As Variant
Dim MachineNames As Variant

    Keywords = Array("SAM ", "Press ", "Robot", "Robot 1", "Robot 2", "Robot 3", "Robot 4", "Robot 5", "Robot 6", "FA ", "FA 1", "FA1", "FA 2", "FA2", "FA 3", "FA3", "FA 4", "FA4", "FA 5", "FA5", "FA 6", "FA6", "FA 7", "FA7", "FA 8", "FA8", "FA 9", "FA9", "FA 10", "FA10", "FA 11", "FA11", "FA 12", "FA12", "St 120", "St 95", "St 90C", "Flex Arc", "Flex Arch", "Hammond", "Acme", "Polish", "Tank", "Fender", "Welder", "Balance", "PICO", "Gravity", "Vin Mark", "Vin Stamp", "Telesis", "Pinstamp", "Pin stamp", "Buff", "Wet", "E-Coat", "E Coat", "Ecoat", "Carrier", "Line", "Line 1", "Line1", "Line 2", "Line2", "Line 3", "Line3", "Line 4", "Line4", "Line 5", "Line5", "Line 6", "Line6", "St 100", "St 30", "St 150", "Laser", "Laser 1", "Laser1", "Laser 2", "Laser2", "Laser 3", "Laser3", "Laser 4", "Laser4", "Laser 5", "Laser5", "Laser 6", "Laser6", "Laser Seamer", "Laser Seam", "Laser Seemer", _
                        "Vin Laser", "Monode", "Sub", "Tip", "Tip Change", "Swingarm Press", "Swing arm Press", "Bearing Press", "Medallion Press", "Footboard Press", "AIDA", "Cushion", "Press 1", "Press1", "Press 2", "Press2", "Press 3", "Press3", "Press 4", "Press4")
    MachineNames = Array("SAM", "Press", "Robot", "Robot 1", "Robot 2", "Robot 3", "Robot 4", "Robot 5", "Robot 6", "FA", "FA 1", "FA 1", "FA 2", "FA 2", "FA 3", "FA 3", "FA 4", "FA 4", "FA 5", "FA 5", "FA 6", "FA 6", "FA 7", "FA 7", "FA 8", "FA 8", "FA 9", "FA 9", "FA 10", "FA 10", "FA 11", "FA 11", "FA 12", "FA 12", "FA 4", "FA 3", "FA 10", "FA", "FA", "Polish (Hammond)", "Polish (Acme)", "Polish", "Tank", "Fender", "Welder", "Balance", "PICO", "Gravity", "Vin Stamp", "Vin Stamp", "Pinstamp", "Pinstamp", "Pinstamp", "Buff", "Wet", "E-Coat", "E-Coat", "E-Coat", "Carrier", "Line", "Line 1", "Line 1", "Line 2", "Line 2", "Line 3", "Line 3", "Line 4", "Line 4", "Line 5", "Line 5", "Line 6", "Line 6", "Laser", "Laser", "Laser", "Laser", "Laser 1", "Laser 1", "Laser 2", "Laser 2", "Laser 3", "Laser 3", "Laser 4", "Laser 4", "Laser 5", "Laser 5", "Laser 6", "Laser 6", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", _
                            "Laser (Vin)", "Laser (Monode)", "Sub", "Tip", "Tip", "Press (Swingarm)", "Press (Swingarm)", "Press (Bearing)", "Press (Medallion)", "Press (Footboard)", "Press (AIDA)", "Press", "Press 1", "Press 1", "Press 2", "Press 2", "Press 3", "Press 3", "Press 4", "Press 4")

    Range("A2").Activate

    Do Until ActiveCell.Offset(0, 10) <> "" 'there are cells filled at the bottom of the sheet already.
        'There are 49 array items, so 0 - 48
        For i = 0 To 48
            Set C = ActiveCell.Find(Keywords(i), LookIn:=xlValues)
                If Not C Is Nothing Then ActiveCell.Offset(0, 10).Value = MachineNames(i)
        Next i
        ActiveCell.Offset(1, 0).Activate
    Loop

我的问题是，有时它会找到价值，而有时却找不到。我意识到数组中项的顺序会导致这种情况。示例: A2包含"Press“B2包含"Line”(*注意，两个单元格都不包含两个字符串)。

当"Press“位于数组列表的后面时，它会打印”其他机器“，但是当它位于数组列表的前面时，它会打印"Press"，但是在过去打印"Line”的地方，它现在会打印“其他机器”。

任何帮助都是非常感谢的！

Answer 1

如果您决定使用VBA，这将是一个更有效的版本-您不需要激活单元格

Option Explicit

Public Sub UpdateMachineNames()

    Dim keywords As Variant
    Dim machineNames As Variant
    Dim k As Long

    keywords = Array("SAM ", "Press ", "Robot", "Robot 1", "Robot 2", "Robot 3", "Robot 4", "Robot 5", "Robot 6", "FA ", "FA 1", "FA1", "FA 2", "FA2", "FA 3", "FA3", "FA 4", "FA4", "FA 5", "FA5", "FA 6", "FA6", "FA 7", "FA7", "FA 8", "FA8", "FA 9", "FA9", "FA 10", "FA10", "FA 11", "FA11", "FA 12", "FA12", "St 120", "St 95", "St 90C", "Flex Arc", "Flex Arch", "Hammond", "Acme", "Polish", "Tank", "Fender", "Welder", "Balance", "PICO", "Gravity", "Vin Mark", "Vin Stamp", "Telesis", "Pinstamp", "Pin stamp", "Buff", "Wet", "E-Coat", "E Coat", "Ecoat", "Carrier", "Line", "Line 1", "Line1", "Line 2", "Line2", "Line 3", "Line3", "Line 4", "Line4", "Line 5", "Line5", "Line 6", "Line6", "St 100", "St 30", "St 150", "Laser", "Laser 1", "Laser1", "Laser 2", "Laser2", "Laser 3", "Laser3", "Laser 4", "Laser4", "Laser 5", "Laser5", "Laser 6", "Laser6", "Laser Seamer", "Laser Seam", "Laser Seemer", _
                     "Vin Laser", "Monode", "Sub", "Tip", "Tip Change", "Swingarm Press", "Swing arm Press", "Bearing Press", "Medallion Press", "Footboard Press", "AIDA", "Cushion", "Press 1", "Press1", "Press 2", "Press2", "Press 3", "Press3", "Press 4", "Press4")

    machineNames = Array("SAM", "Press", "Robot", "Robot 1", "Robot 2", "Robot 3", "Robot 4", "Robot 5", "Robot 6", "FA", "FA 1", "FA 1", "FA 2", "FA 2", "FA 3", "FA 3", "FA 4", "FA 4", "FA 5", "FA 5", "FA 6", "FA 6", "FA 7", "FA 7", "FA 8", "FA 8", "FA 9", "FA 9", "FA 10", "FA 10", "FA 11", "FA 11", "FA 12", "FA 12", "FA 4", "FA 3", "FA 10", "FA", "FA", "Polish (Hammond)", "Polish (Acme)", "Polish", "Tank", "Fender", "Welder", "Balance", "PICO", "Gravity", "Vin Stamp", "Vin Stamp", "Pinstamp", "Pinstamp", "Pinstamp", "Buff", "Wet", "E-Coat", "E-Coat", "E-Coat", "Carrier", "Line", "Line 1", "Line 1", "Line 2", "Line 2", "Line 3", "Line 3", "Line 4", "Line 4", "Line 5", "Line 5", "Line 6", "Line 6", "Laser", "Laser", "Laser", "Laser", "Laser 1", "Laser 1", "Laser 2", "Laser 2", "Laser 3", "Laser 3", "Laser 4", "Laser 4", "Laser 5", "Laser 5", "Laser 6", "Laser 6", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", _
                         "Laser (Vin)", "Laser (Monode)", "Sub", "Tip", "Tip", "Press (Swingarm)", "Press (Swingarm)", "Press (Bearing)", "Press (Medallion)", "Press (Footboard)", "Press (AIDA)", "Press", "Press 1", "Press 1", "Press 2", "Press 2", "Press 3", "Press 3", "Press 4", "Press 4")

    If UBound(keywords) <> UBound(machineNames) Then
        MsgBox "Invalid arrays"
        Exit Sub
    Else
        Application.ScreenUpdating = False

        With ActiveSheet.UsedRange.Columns("J")

            For k = 0 To UBound(keywords)
                .Replace What:=keywords(k), Replacement:=machineNames(k), LookAt:=xlPart, MatchCase:=False
            Next

        End With

        Application.ScreenUpdating = True
    End If
End Sub

如果数组没有相同数量的项，并且每次更改数组时，VBA代码都会通知您，并且更容易维护，而不是更新所有单元格的公式。

此外，如果您有大量的数据，这个公式可以大大增加文件的大小。

Answer 2

我发现我忘了更新我的i = 0 to 48 -> i = 0 to 109。我向数组中添加了更多的项，但忘记了更新该部分！

更新:更好的做法是使用i = 0 to UBound(Keywords)使其具有动态。

不过，我确实发现，可以通过一个公式(对于那些不想使用VBA或需要将其设置为单元格的人)来完成类似的工作：

=IFERROR(INDEX({"SAM","Robot","Robot 1","Robot 2","Robot 3","Robot 4","Robot 5","Robot 6","FA","FA 1","FA 1","FA 2","FA 2","FA 3","FA 3","FA 4","FA 4","FA 5","FA 5","FA 6","FA 6","FA 7","FA 7","FA 8","FA 8","FA 9","FA 9","FA 10","FA 10","FA 11","FA 11","FA 12","FA 12","FA 4","FA 3","FA 10","FA","FA","Polish (Hammond)","Polish (Acme)","Polish","Tank","Fender","Welder","Balance","PICO","Gravity","Vin Stamp","Vin Stamp","Pinstamp","Pinstamp","Pinstamp","Buff","Wet","E-Coat","E-Coat","E-Coat","Carrier","Line","Line 1","Line 1","Line 2","Line 2","Line 3","Line 3","Line 4","Line 4","Line 5","Line 5","Line 6","Line 6","Laser","Laser","Laser","Laser","Laser 1","Laser 1","Laser 2","Laser 2","Laser 3","Laser 3","Laser 4","Laser 4","Laser 5","Laser 5","Laser 6","Laser 6","Laser (Seamer)","Laser (Seamer)","Laser (Seamer)","Laser (Vin)","Laser (Monode)","Sub","Tip","Tip","Press (Swingarm)","Press (Swingarm)","Press (Bearing)","Press (Medallion)","Press (Footboard)","Press (AIDA)","Press","Press 1","Press 1","Press 2","Press 2","Press 3","Press 3","Press 4","Press 4","Press"},
MATCH(TRUE,ISNUMBER(SEARCH({"SAM ","Robot","Robot 1","Robot 2","Robot 3","Robot 4","Robot 5","Robot 6","FA ","FA 1","FA1","FA 2","FA2","FA 3","FA3","FA 4","FA4","FA 5","FA5","FA 6","FA6","FA 7","FA7","FA 8","FA8","FA 9","FA9","FA 10","FA10","FA 11","FA11","FA 12","FA12","St 120","St 95","St 90C","Flex Arc","Flex Arch","Hammond","Acme","Polish","Tank","Fender","Welder","Balance","PICO","Gravity","Vin Mark","Vin Stamp","Telesis","Pinstamp","Pin stamp","Buff","Wet","E-Coat","E Coat","Ecoat","Carrier","Line","Line 1","Line1","Line 2","Line2","Line 3","Line3","Line 4","Line4","Line 5","Line5","Line 6","Line6","St 100","St 30","St 150","Laser","Laser 1","Laser1","Laser 2","Laser2","Laser 3","Laser3","Laser 4","Laser4","Laser 5","Laser5","Laser 6","Laser6","Laser Seamer","Laser Seam","Laser Seemer","Vin Laser","Monode","Sub","Tip","Tip Change","Swingarm Press","Swing arm Press","Bearing Press","Medallion Press","Footboard Press","AIDA","Cushion","Press 1","Press1","Press 2","Press2","Press 3","Press3","Press 4","Press4","Press "},
A1)),0)),"Other Machines")