vba 将单元格设置为等于 0 的条件 If 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17581816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional If statement to set cells equal to 0
提问by Sarah
I have a spreadsheet that is over 6000 rows and 300 columns. I need to know how to write code in vba that will allow me to read cells in a column and if says "no" then it sets the 3 cells to the right of it equal to zero. There is no error when I debug it, but the error is in the cell.Offset line. Thoughts?
我有一个超过 6000 行和 300 列的电子表格。我需要知道如何在 vba 中编写代码，以允许我读取列中的单元格，如果说“否”，则它将其右侧的 3 个单元格设置为零。调试的时候没有报错，但是报错在cell.Offset行。想法？
Thank you in advance
先感谢您
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
回答by chuff
The following code should do the job. Using a For/Next loop, it reads each of the cells in Sheet 1 from A1 to the last cell in column A that has data. If the current cell has a value of "no", then it sets the value of the cell three columns to the right to the value 0.
以下代码应该可以完成这项工作。使用 For/Next 循环，它读取工作表 1 中的每个单元格，从 A1 到 A 列中包含数据的最后一个单元格。如果当前单元格的值为“no”，则它将右侧三列单元格的值设置为值 0。
Sub SetTo0IfNo()
Dim rng As Range
Dim lastRow As Long
Dim cell As Variant
? ? Application.Calculation = xlCalculationManual
? ? Application.ScreenUpdating = False
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
End If
Next
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
To set a range of cells to the right of the column A cells to 0, you would use slightly different syntax that still relies on the offset function. For example, to set the three cells immediately to right to 0, replace the above code line cell.Offset(0,3).Value = 0with the following code.
要将 A 列单元格右侧的一系列单元格设置为 0，您将使用略有不同的语法，但仍然依赖于偏移函数。例如，要将紧邻右侧的三个单元格设置为 0，请将上面的代码行cell.Offset(0,3).Value = 0替换为以下代码。
Range(cell.Offset(0, 1), cell.Offset(0, 3)).Value = 0
This approach is necessary because, unlike the worksheet OFFSETfunction which can return a reference to a range of cells, the VBA OFFSET can refer only to a single cell.
这种方法是必要的，因为与OFFSET可以返回对一系列单元格的引用的工作表函数不同，VBA OFFSET 只能引用单个单元格。
回答by Andy G
Borrowing chuff's code:
借用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
