Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Range Behavior
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Range Behavior
Don't worry, odd but normal. When referring to cells in entire rows or
columns you need add .Cells as you have done. Regards, Peter T "Matthew Pfluger" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange Range Behavior
Thank you for confirming.
Matthew Pfluger "Peter T" wrote: Don't worry, odd but normal. When referring to cells in entire rows or columns you need add .Cells as you have done. Regards, Peter T "Matthew Pfluger" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help: Strange, Strange Behavior ( Not Me. My Model! ) | Excel Programming | |||
Strange behavior | Excel Programming | |||
Strange range behavior (example from Help doesn't work) | Excel Programming | |||
Strange TAB behavior | Excel Programming | |||
Strange Range.item behavior? | Excel Programming |