Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
Hi all,
I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
Not sure what you are trying to achieve, but maybe something like this will
work for you: Sub test() Dim i As Long Dim arr(1 To 1, 1 To 30) As Long For i = 1 To 30 arr(1, i) = i Next i Range("A1:AD1").Value = arr End Sub RBS wrote in message oups.com... Hi all, I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
Hi. It doesn't work like a math program, but here's one way in Excel:
Sub ArrayTest() Dim v1 As Variant Dim v2 As Variant Dim m As Variant v1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) v2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) m = Array(v1, v2) With WorksheetFunction m = .Transpose(.Transpose(m)) End With [A1].Resize(UBound(m, 1), UBound(m, 2)) = m End Sub -- HTH :) Dana DeLouis Windows XP & Office 2007 wrote in message oups.com... Hi all, I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Array function with more than 29 arguments
Dana,
What happened?? You took an array with 2 elements, each of which was a variant holding an array. You transposed it twice and got back a 2-dim array? How does that work? "Dana DeLouis" wrote: Hi. It doesn't work like a math program, but here's one way in Excel: Sub ArrayTest() Dim v1 As Variant Dim v2 As Variant Dim m As Variant v1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) v2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) m = Array(v1, v2) With WorksheetFunction m = .Transpose(.Transpose(m)) End With [A1].Resize(UBound(m, 1), UBound(m, 2)) = m End Sub -- HTH :) Dana DeLouis Windows XP & Office 2007 wrote in message oups.com... Hi all, I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two search arguments in array formula | Excel Worksheet Functions | |||
Array function with more than 29 arguments | Excel Discussion (Misc queries) | |||
Array function with more than 29 arguments | Excel Discussion (Misc queries) | |||
Passing arguments into function to bring data array from closed wb | Excel Programming | |||
How do I set up an array using countif for 2 separate arguments. | Excel Worksheet Functions |