这是正确的版本。谢谢

Public Sub RolloutStage()
    
        Dim rng As Range
        For Each rng In Worksheets("backEnd_Lost&Found").Range("B5:B28")
    
            If rng.Value = "Live" Then
            rng.Offset(0, 1).Value = 8
            
            ElseIf rng.Value = "Configuration" Then
            rng.Offset(0, 1).Value = 7
            
            ElseIf rng.Value = "Testing" Then
            rng.Offset(0, 1).Value = 6
            
            ElseIf rng.Value = "Planned" Then
            rng.Offset(0, 1).Value = 5
            
            ElseIf rng.Value = "Pending" Then
            rng.Offset(0, 1).Value = 4
            
            ElseIf rng.Value = "Not planned" Then
            rng.Offset(0, 1).Value = 3
            
            ElseIf rng.Value = "No contact" Then
            rng.Offset(0, 1).Value = 2
            
            Else
            
            rng.Value = "Not interested"
            rng.Offset(0, 1).Value = 1
            
            End If
        Next
    
    End Sub

有条件地填充相邻单元格

在你的密码里

• 不能将变量用作对象的属性：使用rng.Value代替ws.rng.Value。
• 工作表没有ActiveCell属性：使用rng代替ws.ActiveCell。
  For Each...Next循环
• For Each cell In rg.Cells行是做什么的？你可以想象在第一次迭代中，它在下面写了下面这行不可见的代码：

Set cell = rg.Cells(1) ' B5

所以在下一章中，我们将使用this cell检查值，并再次使用this cell向右边相邻的单元格写入另一个值。

• 在下一次迭代中，不可见线如下所示：

Set cell = rg.Cells(2) ' B6

等等。

进步

Public Sub RolloutStage()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("backEnd_Lost&Found")
    Dim srg As Range: Set srg = ws.Range("B5:B28")

    Dim sCell As Range, dCell As Range
    
    For Each sCell In srg.Cells
        Set dCell = sCell.Offset(, 1)
        Select Case CStr(sCell.Value)
            Case "Live": dCell.Value = 8
            Case "Configuration": dCell.Value = 7
            Case "Testing": dCell.Value = 6
            Case "Planned": dCell.Value = 5
            Case "Pending": dCell.Value = 4
            Case "Not planned": dCell.Value = 3
            Case "No contact": dCell.Value = 2
            Case Else: sCell.Value = "Not interested": dCell.Value = 1
        End Select
    Next sCell
 
End Sub