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

Looping across two parallel ranges
 

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

Looping across two parallel ranges
 

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" wrote in message
...
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

Looping across two parallel ranges
 

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" wrote in message
...
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" wrote in message
...
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

Looping across two parallel ranges
 

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