Looping through Non contiguous cells in a row

[avi]

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

 

[Clif McIrvin]

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

 

[avi]

Thanks but I get the same wrong columns

Avi

 

[Clif McIrvin]

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.

 

[GS]

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.

 

[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
"B11" instead of "B1:C1" and E1

Thanks
Avi

 

[GS]

avi brought next idea :

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
"B11" 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.

 

[Clif McIrvin]

avi brought next idea :

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

 

[GS]

Clif McIrvin presented the following explanation :

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.

 

[avi]

Great!

Many thanks
Avi

 

[Clif McIrvin]

Clif McIrvin presented the following explanation :

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

I tested that by using [ Set MyRange = Selection ] ... which also works,
and allows the user to select whatever combination of cells / areas
desired. The problem, though, is that my code makes the assumption that
each area has the same number of rows.

I also recently did some experimenting with [For Each c in MyRange ...
Next c ] which process all cells in all areas in the range, but
processes each area in turn, so you get all rows in the first area
before moving on to the next area, which disturbs the sequence the OP
wanted.

 

[GS]

I also recently did some experimenting with [For Each c in MyRange ... Next c

] which process all cells in all areas in the range, but processes each area
in turn, so you get all rows in the first area before moving on to the next
area, which disturbs the sequence the OP wanted.

Yes, this was my first approach when I tried to quickly write some code
to help the OP get closer to the desired result. Your code is similar
to what I finally came up with and so is why I didn't post any code.
What makes it work row by row across all the areas is the use of the
Union() function.


Alternate approach:
Since the location of data is hard coded in the OP's example, I
assumed this was the result of how the data was pulled from its source.
In this case, the column positions would be fixed as to the field data
to be collected. That precludes the possibility of storing the field
locations (ie: Col positions) in an array and stepping through each row
of data pulling cell refs from the array. This allows getting the data
using MyRange.Cells(Row#, ColPosition) as in the following example.

<Aircode>
Dim i As Integer, j As Integer
Dim rngSource As Range, rng As Range
Dim vCols As Variant

Set rngSource = Range("B1:E10")
vCols = Array(1, 2, 4)

For Each rng In rngSource.Rows
For j = LBound(vCols) To UBound(vCols)
Debug.Print rngSource.Cells(rng.Row, vCols(j)).Address _
& vbTab & "Value"
Next j
Next rng
Set rngSource = Nothing
</Aircode>

Note that this approach requires less code. -It doesn't require use of
the Areas property of the range object nor use of the Union() function
to set a ref to the range object, AND requires one less loop. This was
where I was originally heading after realizing the Areas approach also
required use of the Union() function. I just didn't see any point in
posting that since your solution got the job done nicely 'as is'. I
guess the OP now has a choice of which approach to go with.

With this alternate approach, both the data area (a contiguous range)
and column positions (select using Ctrl key if non-contiguous) could
also be individually selected by users on the fly, making this approach
very flexible and user friendly.

Prompt for range area:
rngSource = Selection.Address
(OR use Application.InputBox() and specify the range data type)

Prompt for column positions:
Redim the columns array:
ReDim vCols(Selection.Columns.Count)

Put each column position into the array using a For Each loop like:
i = 0 'initialize the counter
For Each rng In Selection.Columns
vCols(i) = rng.Column: i = i + 1
Next rng


regards,