excel - 对每个使用工作表引用

Question

如果 ws下面同时具有范围对象和单元格属性:

dim ws as worksheet 
set ws = sheets("sheet_1")

x = ws.range("A1")
x = ws.cells(1,1)

为什么这不起作用:

dim cel as range
dim rng as range

set rng = 'some range

for each cel in rng
   ws.cel.value = "foo"
next

我要确保正确 sheet正在被引用，使用 ws 似乎是合乎逻辑的作为 cel 的一部分引用以确保 sheets引用是明确的。

Answer 1

您需要包括如何设置 rng ，这是帮助排除故障的重要信息。但通常，这应该有效:

Sub t()
Dim ws As Worksheet
Set ws = Sheets("sheet_1")

Dim cel As Range, rng As Range
Set rng = ws.Range("A1:C100")

For Each cel In rng.Cells
   cel.Value = "foo"
Next
End Sub