VBA Excel；Rows.Count 不计算所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16820593/
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
VBA Excel; Rows.Count not counting all rows
提问by matt
I'm trying to run a script that inserts a number in to a cell based on the cell's color; if the color is red, then insert the # 1. The spread sheet has 380 rows but the script stops running (inserting 1's) at row 346. Script below:
我正在尝试运行一个脚本，根据单元格的颜色将数字插入到单元格中；如果颜色为红色，则插入 #1。电子表格有 380 行，但脚本在第 346 行停止运行（插入 1）。脚本如下：
Sub InsertOne()
Dim endRow As Long
Dim colorD As Range
Dim Cell As Range
endRow = Sheets(1).Cells(Sheets(1).Rows.Count, "C").End(xlUp).Row
'Ensure ending row is at least Row 2
If endRow < 2 Then
endRow = 2
End If
Set colorD = Range("F2", Range("F" & Rows.Count).End(xlUp))
'Loop through each cell in Column D
For Each Cell In colorD
If Cell.Interior.ColorIndex = 3 Then
Cell.Value = 1
End If
Next Cell
End Sub
回答by Santosh
Try below code :
试试下面的代码：
Once you calculate the endRow, you can use that to Set colorDrange.
计算完 endRow 后，您可以使用它来确定Set colorD范围。
Sub InsertOne()
Dim endRow As Long
Dim colorD As Range
Dim Cell As Range
endRow = Sheets(1).Cells(Sheets(1).Rows.Count, "C").End(xlUp).Row
'Ensure ending row is at least Row 2
If endRow < 2 Then
endRow = 2
End If
Set colorD = Range("F2:F" & endRow)
'Loop through each cell in Column D
For Each Cell In colorD
If Cell.Interior.ColorIndex = 3 Then
Cell.Value = 1
End If
Next Cell
End Sub
