How to save contents of cell range in an array?
 

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

How to save contents of cell range in an array?
 

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

Tim

"John Uebersax" wrote in message
...
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

How to save contents of cell range in an array?
 

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" wrote in message
...
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

How to save contents of cell range in an array?
 

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
.

How to save contents of cell range in an array?
 

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
.