I have a do loop that uses an array to find Keywords within a cell and replaces them with their corresponding MachineNames array value. When I run this by itself, it works perfectly. When I run it within a bigger code, it does appear to loop through each of the array items (I used breakpoints to watch this), but for some reason it no longer "sees" the value in the cell. It simply loops through all the Keywords, decides none of them were found (even if one is in the cell)... any thoughts or help is appreciated!
'MACHINENAME (if it is written in the description.)
Dim Keywords As Variant
Dim MachineNames As Variant
Keywords = Array("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", "Sta120", "Sta95", "Sta90C", "Sta 120", "Sta 95", "Sta 90C", "St120", "St95", "St90C", "St 120", "St 95", "St 90C", "Flex Arc", "Flex Arch", _
"Hammond", "Acme", "Polish", "SAM", "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", _
"Sta100", "Sta30", "Sta150", "Sta 100", "Sta 30", "Sta 150", "St100", "St30", "St150", "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", "Press ", "Swingarm Press", "Swing arm Press", "Bearing Press", "Medallion Press", "Footboard Press", "AIDA", "AIDA Press", "Cushion", "Press 1", "Press1", "Press 2", "Press2", "Press 3", "Press3", "Press 4", "Press4")
MachineNames = Array("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 4", "FA 3", "FA 10", "FA 4", "FA 3", "FA 10", "FA 4", "FA 3", "FA 10", "FA", "FA", _
"Polish (Hammond)", "Polish (Acme)", "Polish", "SAM", "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 (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "Laser (Seamer)", "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", "Press (Swingarm)", "Press (Swingarm)", "Press (Bearing)", "Press (Medallion)", "Press (Footboard)", "Press (AIDA)", "Press (AIDA)", "Press", "Press 1", "Press 1", "Press 2", "Press 2", "Press 3", "Press 3", "Press 4", "Press 4")
Range("A2").activate 'the cell to look in for the keyword
Do Until ActiveCell.Offset(0, 10) <> "" 'the cell we are placing the found MachineName Value in (or blank if none are found.) We will eventually hit previous data, so it will stop if this cell is not blank.
For i = 0 To UBound(Keywords)
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
Since you mention that the code doesn't seems to "see" the value in the cell which in your code is introduced by
Range("A2").activate
I would try to replace this with the full reference of the cell which would be something like:
Application.Workbooks("book1").Worksheets("Sheet1").Range("A2").Activate
Replace book1 with your file name and Sheet1 with your worksheet. My assumption is that Range("A2").Activate activates A2 on some other sheet but cannot be sure unless see the whole code. Hope that helps ;)