Hi all,

I am ashamed not to know why this doesn't work... I am
using old Excel 97 SR-2.

The following code happily goes through each cell in the
range from A1 to A5, and prints each address.

Dim rngSELECT As Range
Dim rngRange As Range
Set rngSELECT = Range("A1:A5")

For Each rngRange In rngSELECT
Debug.Print rngRange.Address
Next rngRange

But, if I try to do the following:

Dim rngSELECT As Range
Dim rngRange As Range
With Range("AddSELECT")
Set rngSELECT = .Resize(.Rows.Count - 1, 1).Columns
(1) 'returns $B$17:$B$226
End With

For Each rngRange In rngSELECT
Debug.Print rngRange.Address
Next rngRange

The above "For Each" loop is executed only once and gets
out!!! "Set rngSELECT" sets the rngSELECT to $B$17:$B$226
before "For Each". On the first execution of "For Each"
loop, rngRange.Address somehow returns the whole
$B$17:$B$226!!! Why does it NOT return a single cell
(address)??? I expect its address to be $B$17.

I looked at some previous posts and it might be a problem
to use collection in "For Each" loop in Excel 97...

Thanks for your time,
---
Tetsuya Oguma, Sydney, Australia

To simplify the issue try testing the following code. If it works then
you know that the For each.... procedure is not at fault and you will
have to look at the rest of the code.

Sub myTestSub()

Dim oCell As Range
For Each oCell In Range("B4:B8")
Debug.Print oCell.Address
Next

End Sub


HTH



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I think Tom Ogilvy (also on XL2000) once pointed out the fact that adding
..Cells to the the range identifier may help and cant hurt.

For each rngRange in rngSelect.Cells
debug.print rngRange.address
next

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


gocush wrote:

To simplify the issue try testing the following code. If it works then
you know that the For each.... procedure is not at fault and you will
have to look at the rest of the code.

Sub myTestSub()

Dim oCell As Range
For Each oCell In Range("B4:B8")
Debug.Print oCell.Address
Next

End Sub


HTH



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

Hi,

Yeah, I tested your code (and I have done it myself before
my posting) and the result is as I expected.

Mmmm, I am still not sure...

Thanks,

-----Original Message-----
To simplify the issue try testing the following code. If
it works then
you know that the For each.... procedure is not at fault
and you will
have to look at the rest of the code.

Sub myTestSub()

Dim oCell As Range
For Each oCell In Range("B4:B8")
Debug.Print oCell.Address
Next

End Sub


HTH



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

The way you have declared it, rngSELECT is a Range Object that is a
collection of columns; in this case the single column B17:B226 (although
you can refer to columns outside the declared range with rngSELECT(2),
rngSELECT(3), etc.--that's the way range references work).

If you want to refer to the collection of cells within the column,
substitute

Set rngSELECT = .Resize(.Rows.Count - 1, 1) instead of

Set rngSELECT = .Resize(.Rows.Count - 1, 1).Columns(1)

Or, as has been suggested, use

For Each rngRange in rngSELECT.Cells

Alan Beban


Tetsuya Oguma wrote:

But, if I try to do the following:

Dim rngSELECT As Range
Dim rngRange As Range
With Range("AddSELECT")
Set rngSELECT = .Resize(.Rows.Count - 1, 1).Columns
(1) 'returns $B$17:$B$226
End With

For Each rngRange In rngSELECT
Debug.Print rngRange.Address
Next rngRange

The above "For Each" loop is executed only once and gets
out!!! "Set rngSELECT" sets the rngSELECT to $B$17:$B$226
before "For Each". On the first execution of "For Each"
loop, rngRange.Address somehow returns the whole
$B$17:$B$226!!! Why does it NOT return a single cell
(address)??? I expect its address to be $B$17.

I looked at some previous posts and it might be a problem
to use collection in "For Each" loop in Excel 97...

Thanks for your time,
---
Tetsuya Oguma, Sydney, Australia