vba 如何在excel VBA中获取'Cell'对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11942405/
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
how to get a 'Cell' object in excel VBA
提问by Troy Cosentino
So i was using a for each loop to go through a bunch of rows. I ran into a problem where i was deleting rows and it was causing a row to get skipped so i have changed to a do while loop.
所以我使用 for 每个循环来遍历一堆行。我遇到了一个问题，我正在删除行，它导致一行被跳过，所以我已更改为 do while 循环。
The problem i am having is trying to get a 'cell' object. Before when i had:
我遇到的问题是试图获得一个“细胞”对象。之前我有：
For Each C In Worksheets("Blah").Range("A2:A" & lastRow).Cells
I could do things like
我可以做这样的事情
C.Offset(1, 0)
C.Value = "Troy"
Etc. I tried using:
等我尝试使用：
C = Worksheets("Blah").Cells(iRow, 2)
but that just gives C the value of the cell. How do i get the actual cell object?
但这只是给 C 单元格的值。我如何获得实际的单元格对象？
Thanks
谢谢
回答by Dick Kusleika
When you delete rows, it's always best to go from the bottom up. Your particular problem is that you need the Set keyword to set C to the range object rather than "Let" C to the range Value. Range's have a default property, Value, and when you omit a property that's the property that's used. For that reason, you have to use Set when assigning object variables. Here's an example of looping backward to delete rows and assigning an object variable.
删除行时，最好从下往上进行。您的特殊问题是您需要 Set 关键字将 C 设置为范围对象，而不是“让” C 到范围值。Range 有一个默认属性 Value，当您省略一个属性时，该属性就是所使用的属性。因此，您必须在分配对象变量时使用 Set。这是一个向后循环删除行并分配对象变量的示例。
Sub DeleteRows()
Dim i As Long
Dim rRng As Range
Dim rCell As Range
Set rRng = Sheet1.Range("A1:A9")
For i = rRng.Rows.Count To 1 Step -1
Set rCell = rRng.Cells(i, 1)
If rCell.Value Mod 2 = 1 Then
rCell.EntireRow.Delete
End If
Next i
End Sub
回答by Валерий Зайцев
To get an object reference and not the value itself you need to use 'Set'
要获取对象引用而不是值本身，您需要使用“Set”
Set C = Worksheets("Blah").Cells(iRow, 2)
