Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
for J=1 to NumberOfCols
msgbox MyRange.Rows(I).cells(I,J).value
next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range

Any help
Avi

"avi" wrote in message
...
Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
for J=1 to NumberOfCols
msgbox MyRange.Rows(I).cells(I,J).value
next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range


try changing
msgbox MyRange.Rows(I).cells(I,J).value

to
msgbox MyRange.cells(I,J).value

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Thanks but I get the same wrong columns

Avi

"avi" wrote in message
...
Thanks but I get the same wrong columns

Avi


In that case, we need more information. From your OP I really couldn't
tell what you are really trying to do. How are you creating your range,
for one. More of a step-by-step description of what you are doing would
help.

Also, it is helpful if you keep enough of the OP in the reply to keep
the context ... those of us using NNTP newsreaders instead of a web
interface generally do not have previous postings in the thread
available ... and that includes many, if not most, of the regulars here.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

avi laid this down on his screen :
Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
for J=1 to NumberOfCols
msgbox MyRange.Rows(I).cells(I,J).value
next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range

Any help
Avi

I'm thinking a problem exists with the value held in 'NumberOfCols'.
Are you using 'UsedRange.Columns.Count'? Or some other method to
determine the last column used. I would use a method similar to finding
the last row containing data. Something using 'End' and xlToLeft to
determine NumberOfCols.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
* for J=1 to NumberOfCols
* *msgbox MyRange.Rows(I).cells(I,J).value
* next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range

Any help
Avi

Lets say that I have a non contiguous MyRange"B1:C10","E1:E10" of
numerical values

for I=1 to 10
* for J=1 to 3
* *msgbox MyRange.cells(I,J).value
* next
next

The problem is that for I=1 , J runs accross the contiguous rangerow
"B1:D1" instead of "B1:C1" and E1

Thanks
Avi

avi brought next idea :
Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
* for J=1 to NumberOfCols
* *msgbox MyRange.Rows(I).cells(I,J).value
* next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range
Any help
Avi

Lets say that I have a non contiguous MyRange"B1:C10","E1:E10" of
numerical values

for I=1 to 10
* for J=1 to 3
* *msgbox MyRange.cells(I,J).value
* next
next

The problem is that for I=1 , J runs accross the contiguous rangerow
"B1:D1" instead of "B1:C1" and E1

Thanks
Avi

That's because E1 is the fourth cell in the range. Col4 is D.
Sounds to me like you need to look at using Areas, which can hold
non-contiguous ranges. Then you won't get the col count issue because
you would use a For Each loop to iterate each cell.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
avi brought next idea :
Hello,

I want to loop accross each cell in a row oa a non contiguous
range,
something like

for I=1 to NumberOfRows
for J=1 to NumberOfCols
msgbox MyRange.Rows(I).cells(I,J).value
next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range
Any help
Avi

Lets say that I have a non contiguous MyRange"B1:C10","E1:E10" of
numerical values

for I=1 to 10
for J=1 to 3
msgbox MyRange.cells(I,J).value
next
next

The problem is that for I=1 , J runs accross the contiguous rangerow
"B1:D1" instead of "B1:C1" and E1

Thanks
Avi

That's because E1 is the fourth cell in the range. Col4 is D.
Sounds to me like you need to look at using Areas, which can hold
non-contiguous ranges. Then you won't get the col count issue because
you would use a For Each loop to iterate each cell.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc




Something like this:

Sub example()
Dim MyRange As Range
Dim a As Range
Dim intI As Integer
Dim intJ As Integer

Set MyRange = Union(Range("B1:C10"), Range("E1:E10"))

For intI = 1 To MyRange.Rows.Count
For Each a In MyRange.Areas
For intJ = 1 To a.Columns.Count
Debug.Print a.Cells(intI, intJ).Address
'MsgBox a.Cells(intI, intJ).Value
Next intJ
Next a
Next intI

Set MyRange = Nothing
End Sub

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin presented the following explanation :
"GS" wrote in message
...
avi brought next idea :
Hello,

I want to loop accross each cell in a row oa a non contiguous range,
something like

for I=1 to NumberOfRows
for J=1 to NumberOfCols
msgbox MyRange.Rows(I).cells(I,J).value
next
next

The problem is that the J seems to refer to the contiguous range
starting with first column of the range
Any help
Avi

Lets say that I have a non contiguous MyRange"B1:C10","E1:E10" of
numerical values

for I=1 to 10
for J=1 to 3
msgbox MyRange.cells(I,J).value
next
next

The problem is that for I=1 , J runs accross the contiguous rangerow
"B1:D1" instead of "B1:C1" and E1

Thanks
Avi

That's because E1 is the fourth cell in the range. Col4 is D.
Sounds to me like you need to look at using Areas, which can hold
non-contiguous ranges. Then you won't get the col count issue because you
would use a For Each loop to iterate each cell.

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc




Something like this:

Sub example()
Dim MyRange As Range
Dim a As Range
Dim intI As Integer
Dim intJ As Integer

Set MyRange = Union(Range("B1:C10"), Range("E1:E10"))

For intI = 1 To MyRange.Rows.Count
For Each a In MyRange.Areas
For intJ = 1 To a.Columns.Count
Debug.Print a.Cells(intI, intJ).Address
'MsgBox a.Cells(intI, intJ).Value
Next intJ
Next a
Next intI

Set MyRange = Nothing
End Sub

Very good! That does exactly what the OP asks for. The use of the
Union() function (which I failed to mention) is what makes it work.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc