Cells Iteration

The VBA approach to this problem:

Option Explicit

Sub IterateDataSets()
'Jerry Beaucaire   5/2/2010
'Duplicate a 2nd range of data once for each
'row of data in 1st range of data
Dim LR As Long, Rw As Long, NR As Long
Dim CpyRNG As Range
Application.ScreenUpdating = False

Range("I:N").Clear
LR = Range("A" & Rows.Count).End(xlUp).Row
Set CpyRNG = Range("E2", Range("E2").End(xlDown).End(xlToRight))

    For Rw = 2 To LR
        NR = Range("L" & Rows.Count).End(xlUp).Row + 1
        CpyRNG.Copy Range("L" & NR)
        Range("A" & Rw).Resize(, 3).Copy Range("I" & NR).Resize(CpyRNG.Rows.Count)
    Next Rw

Set CpyRNG = Nothing
Range("I1") = "RESULT"
Range("I1").Font.Bold = True
Application.ScreenUpdating = True
End Sub

(sheet attached)

Originally Posted by jeffreybrown (via Private Message)

I see where you Set CpyRNG and would like to know how I can see the entire range as it is held in memory.

As I step through the code using F8 I know I can open up the Locals window and view the values of the variable plus many other things, but how can I see the CpyRNG?

Jeffrey, please continue the thread discussion in the open forum, as per the Forum Rules. (See rule #4) Thanks.

To simply see the address is easy:

Debug.Print CpyRNG.Address

...will print the range in the Immediate Window (CTRL-G to see it if it's not open already).


You need to see individual values within the CpyRNG? Any particular reason why? That starts to get a bit ugly to read code-wise, and I don't do that very often. If the values inside the CpyRNG are relevant to the copy procedure in some way, I would probably rethink the whole thing, so more info makes it easier to understand what you're thinking.

Edited down.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.