vba Excel奇数行给出值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15638423/
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
Excel odd rows give value
提问by Alex
I am trying to assign a value to all the odd cells in a particular column/range. So far I have the following code taken from another question, but it isnt working:
我正在尝试为特定列/范围中的所有奇数单元格分配一个值。到目前为止，我从另一个问题中提取了以下代码，但它不起作用：
Sub changeClass()
Dim r As Range
Set r = Range("B16").End(xlDown) 'set the range the data resides in
For i = 1 To r.Rows.Count 'merge step
If i Mod 2 = 1 Then 'this checkes to see if i is odd
r.Cells.Value = "value"
End If
Else
r.Cells.Value = "value2"
Next i
End Sub
Basically I need it to add in a value for every cell in the B column from cell 16 down to the last cell i nthe column which has data in. On the even rows the value will be one thing, on the odd it will be another.
基本上我需要它为 B 列中的每个单元格添加一个值，从单元格 16 到该列中包含数据的最后一个单元格。在偶数行上，该值将是一回事，奇怪的是它将是另一回事.
Many thanks!
非常感谢！
回答by Skip Intro
Sub changeClass()
Dim r As Range
Dim i As Integer
For Each r In Range("B16:B24") 'Change this range
i = r.Row
If i Mod 2 = 1 Then 'this checks to see if i is odd
r.Cells.Value = "ODD"
Else
r.Cells.Value = "EVEN"
End If
Next r
End Sub
回答by souldzin
Try this out, I believe it is not working, because you are not acessing each individual cell inside your loop. In the following macro i use 'rng' to represent the entire range of cells, and 'r' to represent a single cell in each increment of the loop.
试试这个，我相信它不起作用，因为你没有访问循环中的每个单独的单元格。在下面的宏中，我使用 'rng' 表示整个单元格范围，并使用 'r' 表示循环的每个增量中的单个单元格。
Sub changeClass()
Dim rng As Range
Dim r As Range
Set rng = Range(Cells(16,2),Cells(16,2).End(xlDown))
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i)
If i Mod 2 = 1 Then ' You may want to test if it is odd based on the row number (depends on your problem...)
r.Value = "Odd Value"
Else
r.Value = "Even Value"
End If
Next i
End Sub
回答by Dan Donoghue
You don't need a loop for this:
您不需要为此循环：
Sub OddRowAlert()
With Range("B16:B100")
.Formula = "=IF((MOD(ROW(B16),2)),""Odd"",""Even"")"
.Formula = .Value
End With
End Sub
Just replace odd and even in the formula with what you want
只需用你想要的替换公式中的奇数和偶数
回答by TheLaurens
you've messed up your if statement, don't close it off before else close it only once you are completely done with it! ;) here:
你搞砸了你的 if 语句，在你完全完成之前不要关闭它，否则关闭它！;） 这里：
Sub changeClass()
Dim r As Range
Set r = Range("B16").End(xlDown) 'set the range the data resides in
For i = 1 To r.Rows.Count 'merge step
If i Mod 2 = 1 Then 'this checkes to see if i is odd
r.Cells.Value = "value"
Else
r.Cells.Value = "value2"
End if
Next i
End Sub
