循环遍历范围内的每个单元格，更快地替换值和添加注释

Question

我有一个代码，它在一个范围内循环，查找值，如果匹配，它会更改单元格值并添加注释。我这样做是为了4个不同的值，所以代码运行需要一段时间。有没有办法让它更快？

我曾考虑过将行放入数组中，但我不确定如何将其应用于我的代码：在VBA(excel(中循环行的最有效/最快方法是什么？

Sub Replace()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cell In Range("F9:F" & Rows.Count)
If (Cell.Value = "Aide-général (REMPLAÇANT)") Then
Cell.Value = "Aide-général"
Cell.AddComment "REMPLAÇANT"
End If
Next Cell
For Each Cell In Range("F9:F" & Rows.Count)
If (Cell.Value = "Cariste") Then
Cell.Value = "Cariste (8300 / 3700)"
Cell.AddComment "Cariste"
End If
Next Cell
For Each Cell In Range("F9:F" & Rows.Count)
If (Cell.Value = "Cariste (3700)") Then
Cell.Value = "Cariste (8300 / 3700)"
Cell.AddComment "Cariste (3700)"
End If
Next Cell
For Each Cell In Range("F9:F" & Rows.Count)
If (Cell.Value = "OP Break&Lunch Torréfaction (3700)") Then
Cell.Value = "Torréfaction (3700)"
Cell.AddComment "Break&Lunch"
End If
Next Cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Answer 1

我用select case替换了if函数，并且只有一个for循环，因此代码只在单元格中循环一次

Sub Replace()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cell In Range("F9:F" & Rows.Count)
Select Case Cell.Value
Case Is = "Aide-général (REMPLAÇANT)"
Cell.Value = "Aide-général"
Cell.AddComment "REMPLAÇANT"
Case Is = "Cariste"
Cell.Value = "Cariste (8300 / 3700)"
Cell.AddComment "Cariste"
Case Is = "Cariste (3700)"
Cell.Value = "Cariste (8300 / 3700)"
Cell.AddComment "Cariste (3700)"
Case Is = "OP Break&Lunch Torréfaction (3700)"
Cell.Value = "Torréfaction (3700)"
Cell.AddComment "Break&Lunch"
Case Else
End Select
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub