excel - 我似乎无法指定执行宏的工作表。无论我做什么，我都会收到错误 9

Question

我有两张床单。“销售和营销”。我想在“销售”表上制定宏。我在第 4 行不断收到错误 9。

Sub halveandcolorchange()
    Dim Sa As Worksheet
    Dim cell As Range, rng As Range, A As Range, LastRow As Long
    
    Set Sa = ThisWorkbook.Worksheets("Sales")

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Sa.Range("B1:B" & LastRow)
    
    For Each cell In rng
        Set A = cell.Offset(0, -1)
        If cell.Value > 3 Then
           A.Value = A.Value / 2
           A.Interior.ColorIndex = 3
        End If
    Next cell
End Sub

标签： excelvba

Answer 1

试试这个代码：

Sub halveandcolorchange()
    Dim Sa As Worksheet
    Dim cell As Range, rng As Range, A As Range, LastRow As Long
    Dim B As Double
    
    Set Sa = ThisWorkbook.Worksheets("Sales")

    LastRow = Sa.Cells(Sa.Rows.Count, "B").End(xlUp).Row
    Set rng = Sa.Range("B1:B" & LastRow)
    
    For Each cell In rng
        Set A = cell.Offset(0, -1)
        If cell.Value > 3 Then
        B = cell.Value
           A.Value = B / 2
           A.Interior.ColorIndex = 3
        End If
    Next cell
End Sub