用于将单元格设置为0的条件If语句

Question

我有一个超过6000行和300列的电子表格。我需要知道如何在vba中编写代码，允许我读取列中的单元格，如果说"no“，那么它会将右边的3个单元格设置为零。我调试它时没有错误，但错误在cell.Offset行。有什么想法？

提前谢谢你

Sub Macro1()

Dim rng As Range
Dim cell As Object

 With Sheets("Sheet1")
    Set rng = .Range("C1:C6000")
    For Each cell In rng
        If cell.Value = "no" Then
            cell.Offset(0, 1).Value = 0
            Exit For
        End If
    Next
 End With
End Sub

Answer 1

借用chuff的代码：

Sub SetTo0IfNo()
    Dim rng As Range
    Dim lastRow As Long
    Dim cell As Range

    With Sheets("Sheet1")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("A1:A" & lastRow)
        For Each cell In rng
            If cell.Value = "no" Then
                'cell.Offset(0, 3).Value = 0
                cell.Range("B1:D1").Value = 0
            End If
        Next
    End With
End Sub