Retrieve values from array and use as cell reference
 

The ancient memory banks are not firing well today!

I need to programmatically generate a range of address, store them in an
array and create a union of the addresses. Kind of as follows:

....

for i = 1 to 20
AddrList(i) = "A"&i
next i

....'more code

for i = lbound(AddrList) to Ubound(AddrList)
UnionRange = Union(UnionRange, AddrList(i))

next i

'more code

' Example additional code:
UnionRange.select

' more code


How do I pass my AddrList values to UnionRange in a way that Union()
understands that it is working on range addresses?

--
Stephen

Due to the volume of garbage I filter out googlegroups.

Retrieve values from array and use as cell reference
 

Cawshus wrote:
The ancient memory banks are not firing well today!

I need to programmatically generate a range of address, store them in an
array and create a union of the addresses. Kind of as follows:

...

for i = 1 to 20
AddrList(i) = "A"&i
next i

...'more code

for i = lbound(AddrList) to Ubound(AddrList)
UnionRange = Union(UnionRange, AddrList(i))

next i

'more code

' Example additional code:
UnionRange.select

' more code


How do I pass my AddrList values to UnionRange in a way that Union()
understands that it is working on range addresses?

--
Stephen

Due to the volume of garbage I filter out googlegroups.



Build a list of ranges not strings
Keep in mind that union args can't be Nothing
I assumed that at least one (first) element on a list is a valid range
if you don't know the exact number of elements use collection instead of
array.


Sub a()

Dim addrlist(1 To 20) As Range

For i = 1 To 3
Set addrlist(i) = Range("A" & i)
Next i

Dim unionrange As Range
Set unionrange = addrlist(LBound(addrlist))
For i = LBound(addrlist) + 1 To UBound(addrlist)
If Not addrlist(i) Is Nothing Then
Set unionrange = Union(unionrange, addrlist(i))
End If
Next i
unionrange.Select
End Sub

Retrieve values from array and use as cell reference
 

witek wrote in
:

Cawshus wrote:
The ancient memory banks are not firing well today!

I need to programmatically generate a range of address, store them in
an array and create a union of the addresses. Kind of as follows:

...

for i = 1 to 20
AddrList(i) = "A"&i
next i

...'more code

for i = lbound(AddrList) to Ubound(AddrList)
UnionRange = Union(UnionRange, AddrList(i))

next i

'more code

' Example additional code:
UnionRange.select

' more code


How do I pass my AddrList values to UnionRange in a way that Union()
understands that it is working on range addresses?

--
Stephen

Due to the volume of garbage I filter out googlegroups.



Build a list of ranges not strings
Keep in mind that union args can't be Nothing
I assumed that at least one (first) element on a list is a valid range
if you don't know the exact number of elements use collection instead
of array.


Sub a()

Dim addrlist(1 To 20) As Range

For i = 1 To 3
Set addrlist(i) = Range("A" & i)
Next i

Dim unionrange As Range
Set unionrange = addrlist(LBound(addrlist))
For i = LBound(addrlist) + 1 To UBound(addrlist)
If Not addrlist(i) Is Nothing Then
Set unionrange = Union(unionrange, addrlist(i))
End If
Next i
unionrange.Select
End Sub



TY

I may have said "memory banks" but suspect it is the logic array that is
not firing well today! Set addrlist(i) = Range("A" & i) should have been
obvious. My first of many attempts was to build a string and Union
(theString). From there, it was every conceivable variation relating to
strings. I had read help on Union. In retrospect it reads "BIG HINT HERE,
old fellow".



--
Stephen

Due to the volume of garbage I filter out googlegroups.