Using for each with a worksheet reference-CodePudding

If ws below has both the range object and cell property:

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

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

Why doesn't this work:

dim cel as range
dim rng as range

set rng = 'some range

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

I want to ensure the right sheet is being referenced, and it seems logical to use ws as part of the cel reference to ensure the sheets reference is explicit.

CodePudding user response：

You need to include how you set the rng, that's a vital bit of info to help troubleshoot. But generally, this should work:

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