M
Matthew Pfluger
I encountered an error in a previously fine loop structure. The loop
operated on each cell in a range:
For Each rng In rngCells
The problem started after I passed a range object to it declared using the
following code:
Set rngTest = Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues +
xlLogical + xlNumbers).Cells(1, 1).CurrentRegion.Rows(1)
I know it's complicated, but here's the thought process. I don't know that
data will bgin in column 1, and I don't know that a table will be continuous
(column gaps). I was trying to find all areas in row 1 and pick the first
area.
For example, if there is data in A1:C1, the code produces the following
debug output:
Name .Count .Cells.Count
rngTest 1 3
rngControl 3 3
If I .Select each object, they select identical ranges. However, when I
pass this rngTest variable to the loop, the rng object is equivalent to the
rngCells object. That is, it doesn't seem to know how to loop. The code
does work if I change the loop code to:
For Each rng In rngCells.Cells
When I tested the following new range variable, the original For Each loop
worked as expected:
Set rngtest2 = Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues +
xlLogical + xlNumbers)
Name .Count .Cells.Count
rngTest2 3 3
rngControl 3 3
So, I believe what is causing this problem is the final Row property. I
would guess that I'm assigning a Range object array instead of a Range
object. Here's the test.
Set rngtest4 = Cells(1, 1).CurrentRegion.Rows(1)
Name .Count .Cells.Count
rngTest4 1 3
Here's a potential fix:
Set rngtest5 = Cells(1, 1).CurrentRegion.Rows(1).Cells
Name .Count .Cells.Count
rngTest5 3 3
This last range runs through my For Each rng in RANGE loop just fine.
Moral of the story: when using the ROW and COLUMN properties to return a
Range object, be sure to test to make sure it returns what you think it does.
Matthew Pfluger
operated on each cell in a range:
For Each rng In rngCells
The problem started after I passed a range object to it declared using the
following code:
Set rngTest = Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues +
xlLogical + xlNumbers).Cells(1, 1).CurrentRegion.Rows(1)
I know it's complicated, but here's the thought process. I don't know that
data will bgin in column 1, and I don't know that a table will be continuous
(column gaps). I was trying to find all areas in row 1 and pick the first
area.
For example, if there is data in A1:C1, the code produces the following
debug output:
Name .Count .Cells.Count
rngTest 1 3
rngControl 3 3
If I .Select each object, they select identical ranges. However, when I
pass this rngTest variable to the loop, the rng object is equivalent to the
rngCells object. That is, it doesn't seem to know how to loop. The code
does work if I change the loop code to:
For Each rng In rngCells.Cells
When I tested the following new range variable, the original For Each loop
worked as expected:
Set rngtest2 = Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues +
xlLogical + xlNumbers)
Name .Count .Cells.Count
rngTest2 3 3
rngControl 3 3
So, I believe what is causing this problem is the final Row property. I
would guess that I'm assigning a Range object array instead of a Range
object. Here's the test.
Set rngtest4 = Cells(1, 1).CurrentRegion.Rows(1)
Name .Count .Cells.Count
rngTest4 1 3
Here's a potential fix:
Set rngtest5 = Cells(1, 1).CurrentRegion.Rows(1).Cells
Name .Count .Cells.Count
rngTest5 3 3
This last range runs through my For Each rng in RANGE loop just fine.
Moral of the story: when using the ROW and COLUMN properties to return a
Range object, be sure to test to make sure it returns what you think it does.
Matthew Pfluger