1

I am using arrays to find certain strings and replace them with an more standard value: 

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

The problem I have, is that sometimes it finds the values, and other times it does not. I realized that the order of the items in the array can cause this. Example: A2 contains "Press " B2 contains "Line" (*Note, neither cell contains both strings.)

When "Press " is at the back of the array list, it prints "Other Machines", but when it is at the front of the array lists, it prints "Press", but then where it used to print "Line" it now prints "Other Machines."

Any help is appreciated!

Improve this question
7
  • 2
    In the code you've posted you say there are 49 array items, but I count 110 - Is that intentional? Commented Oct 24, 2017 at 19:10
  • Hi dwirony! I cant believe I missed that. I added more items to the array this morning and forgot to update the number. I changed it to "i = 0 to 109" and that solved the issues. Thanks a million for your help! Commented Oct 24, 2017 at 19:28
  • 2
    No problem - my suggestion is that next time, instead of having the number 48, use UBound(Keywords) to get your number (when using list arrays)- that way if you ever add/remove items, the number will remain dynamic :) Commented Oct 24, 2017 at 19:31
  • Awesome, that is just what I needed! Thank you again- I really appreciate it :) Commented Oct 24, 2017 at 19:34
  • 1
    You also may want to use an Exit For to exit your for loop when you have found a match, unless you will find another match in the array. Commented Oct 24, 2017 at 19:37

2 Answers 2

Reset to default
2

If you decide to use VBA this would be a more efficient version - you don't need to activate cells

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

The VBA code notifies you if the arrays don't have the same number of items, and is easier to maintain instead of updating the formula for all cells, every time you change the arrays

Also, the formula can increase the file size quite a bit if you have a lot of data

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi paul bica- thanks for this code! I will give it a try as well. I appreciate your help!
0

dwirony saw that I forgot to update my i = 0 to 48 --> i = 0 to 109. I added more items to the arrays but forgot to update the that piece!

Update: Better yet, doing i = 0 to UBound(Keywords) to make it dynamic.

I did however find that something similar can be done via a formula (for those who dont want to work with VBA, or need to set this to a cell):

=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")
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.