Moving range to array

[Charlie]

Hi,

I can move a 2D array to a range but how do I move a selected range to a 2D
array?

Dim MyGrid() As String

Range("A9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
MyGrid = Selection.Text

(or)

MyGrid = Range("B2:D5").Text

Thanks

[Bob Phillips]

Dim MyGrid()


MyGrid = Range("B2:D5")

works for me

--
HTH

Bob Phillips

"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:D4442702-D23E-404F-B54D-96805342908D@microsoft.com...
> Hi,
>
> I can move a 2D array to a range but how do I move a selected range to a
2D
> array?
>
> Dim MyGrid() As String
>
> Range("A9").Select
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> MyGrid = Selection.Text
>
> (or)
>
> MyGrid = Range("B2:D5").Text
>
> Thanks

[Tushar Mehta]

What problem are you having?

If it is type mismatch, use .Value instead of .Text and replace 'As
String' with 'As Variant'.

--
Regards,

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

In article <D4442702-D23E-404F-B54D-96805342908D@microsoft.com>,
Charlie@discussions.microsoft.com says...
> Hi,
>
> I can move a 2D array to a range but how do I move a selected range to a 2D
> array?
>
> Dim MyGrid() As String
>
> Range("A9").Select
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> MyGrid = Selection.Text
>
> (or)
>
> MyGrid = Range("B2:D5").Text
>
> Thanks
>

[Jim Cone]

Charlie,

To add to Bob's post...
You cannot assign to an array in Excel 97.
In later versions you must use the Value property of the
range not the Text property, so...
MyGrid= Range("B2:D5").Value

Jim Cone
San Francisco, USA



"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:D4442702-D23E-404F-B54D-96805342908D@microsoft.com...
Hi,
I can move a 2D array to a range but how do I move a selected range to a 2D
array?
Dim MyGrid() As String
Range("A9").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
MyGrid = Selection.Text
(or)
MyGrid = Range("B2:D5").Text
Thanks

[Tom Ogilvy]

Dim MyGrid as Variant
MyGrid= Range("B2:D5").Value

works fine for me in Excel 97.

Dim MyGrid() as Variant
MyGrid= Range("B2:D5").Value

doesn't work in Excel 97, VBA5

--
Regards,
Tom Ogilvy


"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:eRv4HMMeFHA.3836@tk2msftngp13.phx.gbl...
> Charlie,
>
> To add to Bob's post...
> You cannot assign to an array in Excel 97.
> In later versions you must use the Value property of the
> range not the Text property, so...
> MyGrid= Range("B2:D5").Value
>
> Jim Cone
> San Francisco, USA
>
>
>
> "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> news:D4442702-D23E-404F-B54D-96805342908D@microsoft.com...
> Hi,
> I can move a 2D array to a range but how do I move a selected range to a
2D
> array?
> Dim MyGrid() As String
> Range("A9").Select
> Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> MyGrid = Selection.Text
> (or)
> MyGrid = Range("B2:D5").Text
> Thanks

[Charlie]

Thanks all, it was the array declaration that held me up.

"Tom Ogilvy" wrote:

> Dim MyGrid as Variant
> MyGrid= Range("B2:D5").Value
>
> works fine for me in Excel 97.
>
> Dim MyGrid() as Variant
> MyGrid= Range("B2:D5").Value
>
> doesn't work in Excel 97, VBA5
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
> news:eRv4HMMeFHA.3836@tk2msftngp13.phx.gbl...
> > Charlie,
> >
> > To add to Bob's post...
> > You cannot assign to an array in Excel 97.
> > In later versions you must use the Value property of the
> > range not the Text property, so...
> > MyGrid= Range("B2:D5").Value
> >
> > Jim Cone
> > San Francisco, USA
> >
> >
> >
> > "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> > news:D4442702-D23E-404F-B54D-96805342908D@microsoft.com...
> > Hi,
> > I can move a 2D array to a range but how do I move a selected range to a
> 2D
> > array?
> > Dim MyGrid() As String
> > Range("A9").Select
> > Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
> > MyGrid = Selection.Text
> > (or)
> > MyGrid = Range("B2:D5").Text
> > Thanks
>
>
>