Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does an array contain contents of cell | New Users to Excel | |||
#N/A if cell contents not part of an array | Excel Discussion (Misc queries) | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
Putting contents of an Array into a Cell | Excel Programming | |||
cell contents into an array | Excel Programming |