variant array containing cel adresses convert to actual ranges-array
 

Hi everyone,

In the macro I've already produced so far, which works except for this here
little part, a variant array is filled with absolute cell adresses. When the
macro is finished doing the bulk of the work, a range needs to be selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it anyway,
just to see what I already knew), because the array is Dimmed as a variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter

variant array containing cel adresses convert to actual ranges-array
 

Dim rng as Range
set rng = Range(celadresses(lbound(celadresses)))
for i = lbound(celadresses)+1 to ubound(celadresses)
set rng = union(range(celadresses(i)),rng)
Next
rng.Select

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Hi everyone,

In the macro I've already produced so far, which works except for this
here
little part, a variant array is filled with absolute cell adresses. When
the
macro is finished doing the bulk of the work, a range needs to be selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it
anyway,
just to see what I already knew), because the array is Dimmed as a
variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the
selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter

variant array containing cel adresses convert to actual ranges-array
 

Thanks Tom for your quick reply.

It made perfect sence. However after implementing it into my code it got
this error:

Run-time error '1004':



Method 'Range' of object '_Global' failed


On this line:

Set rng = Range(CelAdresses(LBound(CelAdresses)))

Maybe you could tell me some solutions for this problem.

Again, thanks for what you've already done.

Peter

"Tom Ogilvy" schreef in bericht
...
Dim rng as Range
set rng = Range(celadresses(lbound(celadresses)))
for i = lbound(celadresses)+1 to ubound(celadresses)
set rng = union(range(celadresses(i)),rng)
Next
rng.Select

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Hi everyone,

In the macro I've already produced so far, which works except for this
here
little part, a variant array is filled with absolute cell adresses. When
the
macro is finished doing the bulk of the work, a range needs to be
selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it
anyway,
just to see what I already knew), because the array is Dimmed as a
variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the
selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter

variant array containing cel adresses convert to actual ranges-array
 

Not sure how your data is set up, but would this work for you?

Range(Join(CellAdresses, ",")).Select

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Peter" wrote in message
...
Hi everyone,

In the macro I've already produced so far, which works except for this
here
little part, a variant array is filled with absolute cell adresses. When
the
macro is finished doing the bulk of the work, a range needs to be selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it
anyway,
just to see what I already knew), because the array is Dimmed as a
variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the
selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter

variant array containing cel adresses convert to actual ranges-array
 

I combined this with some modified code I wrote for someone else to populate
the array celadresses. I tested it and it worked fine for me:

Sub Tester1()
Dim rng1 As Range, cell As Range
Dim celadresses() As String, i As Long
Dim rng As Range, ar As Range
For Each cell In Range("A1:C1,D1,B3:F3,J6,J1,F5:F6")
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
Next
Debug.Print rng1.Address
ReDim celadresses(1 To rng1.Areas.Count)
i = 0
For Each ar In rng1.Areas
i = i + 1
celadresses(i) = ar.Address
Debug.Print i, celadresses(i)
Next


Set rng = Range(celadresses(LBound(celadresses)))
For i = LBound(celadresses) + 1 To UBound(celadresses)
Set rng = Union(Range(celadresses(i)), rng)
Next
rng.Select

End Sub





--
Regards,
Tom Ogilvy

Peter wrote in message
...
Thanks Tom for your quick reply.

It made perfect sence. However after implementing it into my code it got
this error:

Run-time error '1004':



Method 'Range' of object '_Global' failed


On this line:

Set rng = Range(CelAdresses(LBound(CelAdresses)))

Maybe you could tell me some solutions for this problem.

Again, thanks for what you've already done.

Peter

"Tom Ogilvy" schreef in bericht
...
Dim rng as Range
set rng = Range(celadresses(lbound(celadresses)))
for i = lbound(celadresses)+1 to ubound(celadresses)
set rng = union(range(celadresses(i)),rng)
Next
rng.Select

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Hi everyone,

In the macro I've already produced so far, which works except for this
here
little part, a variant array is filled with absolute cell adresses.
When
the
macro is finished doing the bulk of the work, a range needs to be
selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it
anyway,
just to see what I already knew), because the array is Dimmed as a
variant.
Switching the Dim statement to make it into a range array also causes
a
runtime error.

How can I use the CellAdresses array with its contents to get the
selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter

variant array containing cel adresses convert to actual ranges-array
 

I suspect that CelAdresses(LBound(CelAdresses)), i.e., the first
element of CelAdresses, does not contain a valid address. What is in
it when you get the error?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Thanks Tom for your quick reply.

It made perfect sence. However after implementing it into my code it got
this error:

Run-time error '1004':



Method 'Range' of object '_Global' failed


On this line:

Set rng = Range(CelAdresses(LBound(CelAdresses)))

Maybe you could tell me some solutions for this problem.

Again, thanks for what you've already done.

Peter

"Tom Ogilvy" schreef in bericht
...
Dim rng as Range
set rng = Range(celadresses(lbound(celadresses)))
for i = lbound(celadresses)+1 to ubound(celadresses)
set rng = union(range(celadresses(i)),rng)
Next
rng.Select

--
Regards,
Tom Ogilvy

"Peter" wrote in message
...
Hi everyone,

In the macro I've already produced so far, which works except for this
here
little part, a variant array is filled with absolute cell adresses. When
the
macro is finished doing the bulk of the work, a range needs to be
selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it
anyway,
just to see what I already knew), because the array is Dimmed as a
variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the
selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter