Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping in VB with cell ranges | Excel Worksheet Functions | |||
Any easier way than looping on ranges | Excel Programming | |||
looping through ranges: how to speed up? | Excel Programming | |||
Looping through Ranges of Rows | Excel Programming | |||
Looping through Ranges of Rows | Excel Programming |