Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
Good Morning Everybody,
I'm sure I've seen this in a post somewhere but for the life of me I can not find it ! so here goes. I have a 2D array... Dim testarray() ReDim testarray(1 To 5, 1 To 20) testarray() = Range("B9:L9").Value The above line fills the 1st row of the array, BUT HOW DO I FILL THE THE OTHER ARRAY ROWS USING CELL RANGES ? Thanks for your help....again Donna |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
So, I've filled my first row with...
testarray() = Range("B9:L13") If I so the following for my 2nd row.... testarray() = Range("B14:L14") it overwrites my 1st row of data. So, what syntax should i be using? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
Donna, do not dimension yoiur array but use 'simple' variant. dim arrHOR as VARIANT dim arrVER as variant dim arrSQR as variant arrHOR = Range("a1:f1").Value fills the variant with a 1 dimensional array arrVER = Range("a1:a10").Value arrSQR = Range("a1:f10") .Value both fill the variant with a 2 dimensional array -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : So, I've filled my first row with... testarray() = Range("B9:L13") If I so the following for my 2nd row.... testarray() = Range("B14:L14") it overwrites my 1st row of data. So, what syntax should i be using? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
I'm sorry I've told you the wrong thing! (Early morning blues)
I've hust tried to see how your posts would help me.......and noticed I've posted totally the wrong scenario. My apologies. Dim testarray() ReDim testarray(1 To 5, 1 To 256) testarray() = Range("B9:L9").Value testarray() = Range("B14:L14").Value End Sub This is what my first post should have been where the 2nd line ovrwrites the array data. I want to copy 5 ranges into my Array. The length of each range is identical but not known before hand hence 1 to 256. Sorry to bother you again. I can do it by copying the ranges to a temp sheet where they are all coincident and then put them into the array but want to avoid this if possible. Cheers Chaps. Donna |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
Extra Question:
I then want to paste all Array data into one long column in the worksheet. Can this be done by pasting an array column by the next array column or not. Again I know i can do it with nested FOR statements easily but want to understand alternative methods there are of inputting and retreiving sets of data from Arrays. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
in that case I'd take a different route.. if you do not know the size you need beforehand i'd dispense with the arrays and use a dictionary object to collect the data. set a reference to Microsoft Scripting Runtime Sub DumpIn1Column() Dim d As New Scripting.Dictionary Dim r As Variant, c As Range, i As Long For Each r In Array(Range("b9:l9"), Range("b14:l14")) For Each c In r.Cells If Len(c) Then i = i + 1 d.Add i, c.Value End If Next Next Range("z1").Resize(UBound(d.Items) + 1, _ 1) = Application.Transpose(d.Items) End Sub note: application.Transpose allows for 5761 items in all versions more (unlimited) in xl2002 and xl2003, but looking at your question it should be more than enough. no more *extra* questions.. please! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : Extra Question: I then want to paste all Array data into one long column in the worksheet. Can this be done by pasting an array column by the next array column or not. Again I know i can do it with nested FOR statements easily but want to understand alternative methods there are of inputting and retreiving sets of data from Arrays. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
Thanks for the replys.
Unfortunately I am off home now so I will continue with this on monday and no doubt be continuing my postings! Thanks again. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
I think arrVer is still two dimensions:
arrHOR = Range("a1:f1").Value fills the variant with a 1 dimensional array But if the range is small (or you're running xl2002+), then this gave me a one dimensional array. With Application arrHOR = .Transpose(.Transpose(Range("a1:f1").Value)) End With keepITcool wrote: Donna, do not dimension yoiur array but use 'simple' variant. dim arrHOR as VARIANT dim arrVER as variant dim arrSQR as variant arrHOR = Range("a1:f1").Value fills the variant with a 1 dimensional array arrVER = Range("a1:a10").Value arrSQR = Range("a1:f10") .Value both fill the variant with a 2 dimensional array -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : So, I've filled my first row with... testarray() = Range("B9:L13") If I so the following for my 2nd row.... testarray() = Range("B14:L14") it overwrites my 1st row of data. So, what syntax should i be using? -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
Dave Peterson wrote:
I think arrVer is still two dimensions: arrHOR = Range("a1:f1").Value fills the variant with a 1 dimensional array I think "1 dimensional" was a typo; intended "2 dimensional" Alan Beban But if the range is small (or you're running xl2002+), then this gave me a one dimensional array. With Application arrHOR = .Transpose(.Transpose(Range("a1:f1").Value)) End With |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
The code below will ignore blanks in the ranges. To transfer the blanks
to the array, delete the 2 lines If Len(c) Then and End If Alan Beban keepITcool wrote: in that case I'd take a different route.. if you do not know the size you need beforehand i'd dispense with the arrays and use a dictionary object to collect the data. set a reference to Microsoft Scripting Runtime Sub DumpIn1Column() Dim d As New Scripting.Dictionary Dim r As Variant, c As Range, i As Long For Each r In Array(Range("b9:l9"), Range("b14:l14")) For Each c In r.Cells If Len(c) Then i = i + 1 d.Add i, c.Value End If Next Next Range("z1").Resize(UBound(d.Items) + 1, _ 1) = Application.Transpose(d.Items) End Sub note: application.Transpose allows for 5761 items in all versions more (unlimited) in xl2002 and xl2003, but looking at your question it should be more than enough. no more *extra* questions.. please! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote : Extra Question: I then want to paste all Array data into one long column in the worksheet. Can this be done by pasting an array column by the next array column or not. Again I know i can do it with nested FOR statements easily but want to understand alternative methods there are of inputting and retreiving sets of data from Arrays. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Fill 2D Arrays Easily
wrote in message
oups.com... I'm sorry I've told you the wrong thing! (Early morning blues) I've hust tried to see how your posts would help me.......and noticed I've posted totally the wrong scenario. My apologies. Dim testarray() ReDim testarray(1 To 5, 1 To 256) testarray() = Range("B9:L9").Value testarray() = Range("B14:L14").Value End Sub This is what my first post should have been where the 2nd line ovrwrites the array data. I want to copy 5 ranges into my Array. The length of each range is identical but not known before hand hence 1 to 256. try this: dim testaray(5) as variant testaray(1)=range("a1:a100").value testaray(2)=range("b1:b250").value run code with debug ["step_into"] and watch the "locals" window. idea is to have "array of arrays", so each row may have different size. since this is "array of arrays", to access data you need [for example]: testaray(1)(99) you are not limited to fixed rows*cols matrix! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sum this easily...? | Excel Worksheet Functions | |||
anyway around this, easily? | Excel Worksheet Functions | |||
how to count easily | Excel Worksheet Functions | |||
How to easily put a sum of all pages | Excel Discussion (Misc queries) | |||
Can this be done easily? | Excel Discussion (Misc queries) |