Looping across two parallel ranges

I'm reaonably new to VBA in Excel, although I've done some icoding in Word
and Access.

Problem
--------
I want to take the text contents of cells in one named range (call it "A"),
and apply them as notes to a range of the same size elsewhere (Call this
range "B").

The two ranges could be of any rectangular shape, but the two ranges will
always be the same shape.

I'm having difficulty getting my head around how to use ranges.

If I get a handle on each range as an array, like:

src= Range("A")
tgt= Range("B")

then I only seem to be able to access the basic value of the cell. I.e.
"tgt" isn't an object, so I can't get to it's Comment property.

Alternatively if I get a Cell Range object with:

src= Range("A").Cells
tgt= Range("B").Cells

I'm having trouble getting the count of the rows/columns in each range so I
can write a nested loop to work through all the Cells.

This has to be simple but I havn't found the right incantation yet. Most
examples use something like:

For Each C In Range("A").Cells
....
Next

This is great for accessing cells in one range, but not easy to work between
two ranges at the same time. Ideas/pointers would be great.

Regards: Colin

Dim cell as Range, i as Long
Dim C as Range
i = 0
For Each C In Range("A").Cells
set cell = Range("B")(i)
msgbox C.Address & " - " & cell.Address
Next

Also, if you want a range reference you need to use set

> src= Range("A")
> tgt= Range("B")

should be

set src= Range("A")
set tgt= Range("B")

--
Regards,
Tom Ogilvy




"colin_e" <coline@discussions.microsoft.com> wrote in message
news:881512DF-054E-4312-AFE5-B754385C10C5@microsoft.com...
> I'm reaonably new to VBA in Excel, although I've done some icoding in Word
> and Access.
>
> Problem
> --------
> I want to take the text contents of cells in one named range (call it
"A"),
> and apply them as notes to a range of the same size elsewhere (Call this
> range "B").
>
> The two ranges could be of any rectangular shape, but the two ranges will
> always be the same shape.
>
> I'm having difficulty getting my head around how to use ranges.
>
> If I get a handle on each range as an array, like:
>
> src= Range("A")
> tgt= Range("B")
>
> then I only seem to be able to access the basic value of the cell. I.e.
> "tgt" isn't an object, so I can't get to it's Comment property.
>
> Alternatively if I get a Cell Range object with:
>
> src= Range("A").Cells
> tgt= Range("B").Cells
>
> I'm having trouble getting the count of the rows/columns in each range so
I
> can write a nested loop to work through all the Cells.
>
> This has to be simple but I havn't found the right incantation yet. Most
> examples use something like:
>
> For Each C In Range("A").Cells
> ....
> Next
>
> This is great for accessing cells in one range, but not easy to work
between
> two ranges at the same time. Ideas/pointers would be great.
>
> Regards: Colin

When you're dealing with objects (including ranges), you need to use the Set
statement:

Dim src as range
dim tgt as range

set src = range("a")
set tgt = range("b")

'if it's a single area, you can use:
msgbox src.columns.count & vblf & src.rows.count

This might give you some ideas:

Option Explicit
Sub testme()

Dim iRow As Long
Dim iCol As Long
Dim src As Range
Dim tgt As Range

Set src = Range("a")
Set tgt = Range("b")

'or just to make sure...
Set src = Range("a").Areas(1)
Set tgt = Range("b").Cells(1, 1).Resize(src.Rows.Count, src.Columns.Count)

tgt.ClearComments 'clean any existing comments

For iRow = 1 To src.Rows.Count
For iCol = 1 To src.Columns.Count
tgt.Cells(iRow, iCol).AddComment Text:=src.Cells(iRow, iCol).Value
Next iCol
Next iRow

End Sub


colin_e wrote:
>
> I'm reaonably new to VBA in Excel, although I've done some icoding in Word
> and Access.
>
> Problem
> --------
> I want to take the text contents of cells in one named range (call it "A"),
> and apply them as notes to a range of the same size elsewhere (Call this
> range "B").
>
> The two ranges could be of any rectangular shape, but the two ranges will
> always be the same shape.
>
> I'm having difficulty getting my head around how to use ranges.
>
> If I get a handle on each range as an array, like:
>
> src= Range("A")
> tgt= Range("B")
>
> then I only seem to be able to access the basic value of the cell. I.e.
> "tgt" isn't an object, so I can't get to it's Comment property.
>
> Alternatively if I get a Cell Range object with:
>
> src= Range("A").Cells
> tgt= Range("B").Cells
>
> I'm having trouble getting the count of the rows/columns in each range so I
> can write a nested loop to work through all the Cells.
>
> This has to be simple but I havn't found the right incantation yet. Most
> examples use something like:
>
> For Each C In Range("A").Cells
> ....
> Next
>
> This is great for accessing cells in one range, but not easy to work between
> two ranges at the same time. Ideas/pointers would be great.
>
> Regards: Colin

--

Dave Peterson

Whoops left a critical line out worry about declaring variables.

Dim cell as Range, i as Long
Dim C as Range
i = 0
For Each C In Range("A").Cells
set cell = Range("B")(i)
msgbox C.Address & " - " & cell.Address
i = i + 1
Next


--
Regards,
Tom Ogilvy


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:e2PQtpLPGHA.3508@TK2MSFTNGP10.phx.gbl...
> Dim cell as Range, i as Long
> Dim C as Range
> i = 0
> For Each C In Range("A").Cells
> set cell = Range("B")(i)
> msgbox C.Address & " - " & cell.Address
> Next
>
> Also, if you want a range reference you need to use set
>
> > src= Range("A")
> > tgt= Range("B")
>
> should be
>
> set src= Range("A")
> set tgt= Range("B")
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "colin_e" <coline@discussions.microsoft.com> wrote in message
> news:881512DF-054E-4312-AFE5-B754385C10C5@microsoft.com...
> > I'm reaonably new to VBA in Excel, although I've done some icoding in
Word
> > and Access.
> >
> > Problem
> > --------
> > I want to take the text contents of cells in one named range (call it
> "A"),
> > and apply them as notes to a range of the same size elsewhere (Call this
> > range "B").
> >
> > The two ranges could be of any rectangular shape, but the two ranges
will
> > always be the same shape.
> >
> > I'm having difficulty getting my head around how to use ranges.
> >
> > If I get a handle on each range as an array, like:
> >
> > src= Range("A")
> > tgt= Range("B")
> >
> > then I only seem to be able to access the basic value of the cell. I.e.
> > "tgt" isn't an object, so I can't get to it's Comment property.
> >
> > Alternatively if I get a Cell Range object with:
> >
> > src= Range("A").Cells
> > tgt= Range("B").Cells
> >
> > I'm having trouble getting the count of the rows/columns in each range
so
> I
> > can write a nested loop to work through all the Cells.
> >
> > This has to be simple but I havn't found the right incantation yet. Most
> > examples use something like:
> >
> > For Each C In Range("A").Cells
> > ....
> > Next
> >
> > This is great for accessing cells in one range, but not easy to work
> between
> > two ranges at the same time. Ideas/pointers would be great.
> >
> > Regards: Colin
>
>

Great stuff. Thanks for the replies Dave and Tom. I'm sure this will get me
going.

When looking at the structures interactively in the debugger I hadn't
navigated far enough down to find the key items- src.Rows.Count,
src.Columns.Count.

Regards: Colin

"Dave Peterson" wrote:

> When you're dealing with objects (including ranges), you need to use the Set
> statement:
>
> Dim src as range
> dim tgt as range
>
> set src = range("a")
> set tgt = range("b")
>
> 'if it's a single area, you can use:
> msgbox src.columns.count & vblf & src.rows.count
>
> This might give you some ideas:
>
> Option Explicit
> Sub testme()
>
> Dim iRow As Long
> Dim iCol As Long
> Dim src As Range
> Dim tgt As Range
>
> Set src = Range("a")
> Set tgt = Range("b")
>
> 'or just to make sure...
> Set src = Range("a").Areas(1)
> Set tgt = Range("b").Cells(1, 1).Resize(src.Rows.Count, src.Columns.Count)
>
> tgt.ClearComments 'clean any existing comments
>
> For iRow = 1 To src.Rows.Count
> For iCol = 1 To src.Columns.Count
> tgt.Cells(iRow, iCol).AddComment Text:=src.Cells(iRow, iCol).Value
> Next iCol
> Next iRow
>
> End Sub
>
>
> colin_e wrote:
> >
> > I'm reaonably new to VBA in Excel, although I've done some icoding in Word
> > and Access.
> >
> > Problem
> > --------
> > I want to take the text contents of cells in one named range (call it "A"),
> > and apply them as notes to a range of the same size elsewhere (Call this
> > range "B").
> >
> > The two ranges could be of any rectangular shape, but the two ranges will
> > always be the same shape.
> >
> > I'm having difficulty getting my head around how to use ranges.
> >
> > If I get a handle on each range as an array, like:
> >
> > src= Range("A")
> > tgt= Range("B")
> >
> > then I only seem to be able to access the basic value of the cell. I.e.
> > "tgt" isn't an object, so I can't get to it's Comment property.
> >
> > Alternatively if I get a Cell Range object with:
> >
> > src= Range("A").Cells
> > tgt= Range("B").Cells
> >
> > I'm having trouble getting the count of the rows/columns in each range so I
> > can write a nested loop to work through all the Cells.
> >
> > This has to be simple but I havn't found the right incantation yet. Most
> > examples use something like:
> >
> > For Each C In Range("A").Cells
> > ....
> > Next
> >
> > This is great for accessing cells in one range, but not easy to work between
> > two ranges at the same time. Ideas/pointers would be great.
> >
> > Regards: Colin
>
> --
>
> Dave Peterson
>