Excel VBA --我的代码本身运行良好，但在更大的代码中却不能运行。

Question

我有一个do循环，它使用数组来查找单元格中的关键字，并用相应的MachineNames数组值替换它们。当我自己运行它的时候，它工作得很完美。当我在一个更大的代码中运行它时，它确实会循环遍历每个数组项(我使用断点来观察它)，但由于某种原因，它不再“看到”单元格中的值。它简单地循环遍历所有关键字，确定没有找到任何关键字(即使其中一个在单元格中).任何想法或帮助都是非常感谢的！

'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

Answer 1

因为您提到代码似乎并没有“看到”代码中引入的单元格中的值。

Range("A2").activate

我试着用这个单元格的全部引用来代替它，它应该是这样的：

Application.Workbooks("book1").Worksheets("Sheet1").Range("A2").Activate

用文件名替换book1，用工作表替换Sheet1。我的假设是，范围(“A2”).Activate会激活其他工作表上的A2，但除非看到整个代码，否则无法确定。希望有帮助;)