I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up with
something like this:
J K L T U


313 1 1 1 1 1
314 2 2 2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315 range,
in which case I lose the count of the rows. (in the actual case, the
values to be placed in the rows is much more difficult to re-compute
then the row number). What I need to do, is iterate through row 313
(J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

Matthew -

Does each row contain the same number? Do all the areas contain the same
rows?

I'm thinking you could iterate through the rows of the worksheet, and in
each row, do something like

for each wsRow in ActiveSheet.Rows
for each rArea in rDiscontiguousRange.Areas
if not intersect(rArea, wsRows(i)) is nothing then
intersect(rArea,wsRow).value = NumberForThatRow
end if
next
next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Matthew Wieder wrote:

I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up with
something like this:
J K L T U


313 1 1 1 1 1
314 2 2 2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315 range,
in which case I lose the count of the rows. (in the actual case, the
values to be placed in the rows is much more difficult to re-compute
then the row number). What I need to do, is iterate through row 313
(J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

set rng = Range("J313:L315,T313:U315")

for i = 313 to 315

for each cell in Intersect(rows(i),rng).Cells
cell.Value = i - 313
Next

Next

--
Regards,
Tom Ogivy

"Matthew Wieder" wrote in message
...
I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up with
something like this:
J K L T U


313 1 1 1 1 1
314 2 2 2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315 range,
in which case I lose the count of the rows. (in the actual case, the
values to be placed in the rows is much more difficult to re-compute
then the row number). What I need to do, is iterate through row 313
(J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

Each cell does not contain the same values as the others in its row (my
fault for the simple example). Each cell's value is caluculated based
on the values of the other cells of that row in the range.
thanks!

Jon Peltier wrote:

Matthew -

Does each row contain the same number? Do all the areas contain the same
rows?

I'm thinking you could iterate through the rows of the worksheet, and in
each row, do something like

for each wsRow in ActiveSheet.Rows
for each rArea in rDiscontiguousRange.Areas
if not intersect(rArea, wsRows(i)) is nothing then
intersect(rArea,wsRow).value = NumberForThatRow
end if
next
next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Matthew Wieder wrote:

I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up
with something like this:
J K L T U


313 1 1 1 1 1 314 2 2
2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315
range, in which case I lose the count of the rows. (in the actual
case, the values to be placed in the rows is much more difficult to
re-compute then the row number). What I need to do, is iterate
through row 313 (J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

Hi Matthew,

Thanks for participating the community!

From my understanding to this issue, you ar going to perform some calculations on two non-contiguous ranges.

I'd suggest you can use one Range array to store each range. Then with the help of one loop, it will be easily for you to iterate the value in the
two ranges. I create one sample for you.
'Code begin ----------------------------------------------------
Sub NoncontiguousRange()

Dim objSht As Worksheet
Dim oRng As Range
Dim oRng2nd As Range
Dim oRngRlt As Range
Dim oCell As Range

Set objSht = Application.ActiveSheet
Set oRng = objSht.Range("D5:F7")
Set oRng2nd = objSht.Range("I5:J7")
Dim oRngArr(2) As Range
Set oRngArr(1) = oRng
Set oRngArr(2) = oRng2nd

Dim oRngTmp As Range
For i = 1 To UBound(oRngArr)
For Each oCell In oRngArr(i).Rows(1).Cells
Debug.Print oCell.Value
Next
Next

End Sub
'Code end ------------------------------------------------------

Please feel free to let me know if you have any questions.

Have a nice day!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Should we just make up how its value is calculated - as long as it uses the
values of other cells in the row?

--
Regards,
Tom Ogilvy

"Matthew Wieder" wrote in message
...
Each cell does not contain the same values as the others in its row (my
fault for the simple example). Each cell's value is caluculated based
on the values of the other cells of that row in the range.
thanks!

Jon Peltier wrote:

Matthew -

Does each row contain the same number? Do all the areas contain the same
rows?

I'm thinking you could iterate through the rows of the worksheet, and in
each row, do something like

for each wsRow in ActiveSheet.Rows
for each rArea in rDiscontiguousRange.Areas
if not intersect(rArea, wsRows(i)) is nothing then
intersect(rArea,wsRow).value = NumberForThatRow
end if
next
next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Matthew Wieder wrote:

I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up
with something like this:
J K L T U


313 1 1 1 1 1 314 2 2
2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315
range, in which case I lose the count of the rows. (in the actual
case, the values to be placed in the rows is much more difficult to
re-compute then the row number). What I need to do, is iterate
through row 313 (J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

That's fine. We can assume it's a sum of all of that rows cells in that
range to the left of teh current cell.

Tom Ogilvy wrote:
Should we just make up how its value is calculated - as long as it uses the
values of other cells in the row?

Sub AABBCC()

Dim rng As Range, rng1 As Range
Dim i As Long, cell As Range
Set rng = Range("J313:L315,T313:U315")

For i = 313 To 315
Set rng1 = Intersect(Rows(i), rng).Cells
rng1.ClearContents
For Each cell In rng1
If cell.Address = rng1(1).Address Then
cell.Value = 1
Else
cell.Value = Application.Sum(rng1)
End If
Next
Next

End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
set rng = Range("J313:L315,T313:U315")

for i = 313 to 315

for each cell in Intersect(rows(i),rng).Cells
cell.Value = i - 313
Next

Next

--
Regards,
Tom Ogivy

"Matthew Wieder" wrote in message
...
I have a complex calculation that I need to write in VBA which can be
represented with the following example. I need to iterate through all
the cells in a noncontiguous range and starting from 1, put the number
of the rows iterated through into the cells in that row so I end up with
something like this:
J K L T U


313 1 1 1 1 1
314 2 2 2 2 2
315 3 3 3 3 3

The problem is that the method for iterating through a noncontiguous
range treats the cells as unrelated and hence I would have to iterate
through the J313:L315 range and only then go on to the T313:U315 range,
in which case I lose the count of the rows. (in the actual case, the
values to be placed in the rows is much more difficult to re-compute
then the row number). What I need to do, is iterate through row 313
(J-L, T-U), then row 314(same) then row 315(same).

Is there an easier way to do this?
thanks!

Sub AABBCC()

Dim rng As Range, rng1 As Range
Dim i As Long, cell As Range
Set rng = Range("J313:L315,T313:U315")

For i = 313 To 315
Set rng1 = Intersect(Rows(i), rng).Cells
rng1.ClearContents
For Each cell In rng1
If cell.Address = rng1(1).Address Then
cell.Value = 1
Else
cell.Value = Application.Sum(rng1)
End If
Next
Next

End Sub

--
Regards,
Tom Ogilvy
"Matthew Wieder" wrote in message
...
That's fine. We can assume it's a sum of all of that rows cells in that
range to the left of teh current cell.

Tom Ogilvy wrote:
Should we just make up how its value is calculated - as long as it uses
the
values of other cells in the row?