In a VBA subroutine, how can one, for example, save the contents
(numbers) of the range A1:L12 in the array x(12,12)?

And how does one declare x()?

Thanks in advance.

John Uebersax

dim x as variant
x = Activesheet.Range("A1:L12").value

Tim

"John Uebersax" <jsuebersax@gmail.com> wrote in message
news:8ef21027-a145-4022-8940-843b4cf2c943@j9g2000prh.googlegroups.com...
> In a VBA subroutine, how can one, for example, save the contents
> (numbers) of the range A1:L12 in the array x(12,12)?
>
> And how does one declare x()?
>
> Thanks in advance.
>
> John Uebersax

Try it this way...

Dim V As Variant
V = Range("A1:C5")
MsgBox V(2, 4) ' Arguments => Row first, then Column

Just give V the scope you want it to have (that is, Dim it at a level that
gives access to whatever procedures you want to know about it).

--
Rick (MVP - Excel)


"John Uebersax" <jsuebersax@gmail.com> wrote in message
news:8ef21027-a145-4022-8940-843b4cf2c943@j9g2000prh.googlegroups.com...
> In a VBA subroutine, how can one, for example, save the contents
> (numbers) of the range A1:L12 in the array x(12,12)?
>
> And how does one declare x()?
>
> Thanks in advance.
>
> John Uebersax

Hi John,

I would use a dynamic array, so it's easy when you have to change your range.

dim aNumbers() as long
dim lNumber as long
dim lCol as long
dim lRow as long

For lRow = 1 to 12
For lCol = 1 to 12
Redim Preserve aNumbers(lRow - 1, lCol -1)
aNumbers(lRow - 1, lCol - 1 = Cells(lRow, lCol).value
Next
Next

"John Uebersax" wrote:

> In a VBA subroutine, how can one, for example, save the contents
> (numbers) of the range A1:L12 in the array x(12,12)?
>
> And how does one declare x()?
>
> Thanks in advance.
>
> John Uebersax
> .
>

Hi John,

I have included a little extra on how to read the values back. I have
assumed that you want the array to have 2 dimensions (across and down) and
that you don't want the values all in a single dimension.

Sub AssignRngToArray()

Dim rngArray()
Dim i As Long
Dim j As Long

'Don't forget .Value on end or it does not work
rngArray = ActiveSheet.Range("A1:C5").Value

For i = 1 To UBound(rngArray, 1) 'Number of elements down
For j = 1 To UBound(rngArray, 2) 'Number of elements accross
MsgBox rngArray(i, j)
Next j
Next i

End Sub

--
Regards,

OssieMac


"John Uebersax" wrote:

> In a VBA subroutine, how can one, for example, save the contents
> (numbers) of the range A1:L12 in the array x(12,12)?
>
> And how does one declare x()?
>
> Thanks in advance.
>
> John Uebersax
> .
>