Hello,
I have a dynamic array which I need to paste in excel
![]()
a(1 To 7, 1 To i)
How can I do that?
Thanks,
BK
Hello,
I have a dynamic array which I need to paste in excel
![]()
a(1 To 7, 1 To i)
How can I do that?
Thanks,
BK
You can use UBound (& LBound where nec.)
If you know you will always use Base 1 then:![]()
Range("A1").Resize(UBound(a,1)+1-LBound(a,1),UBound(a,2)+1-LBound(a,2)).Value = a
![]()
Range("A1").Resize(UBound(a,1),UBound(a,2)).Value = a
Last edited by DonkeyOte; 02-02-2011 at 06:35 AM. Reason: typo - last ref in first code should have been L rather than U
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
The problem is that using Ubound gives me always 7 as the array is defined like this:
a(1 to 7 , 1 to i)
Not if you specify the dimension it doesn't (it defaults to dimension 1 if unspecified)
Did you test the example ?
I tested it but could not make it to work.
Attached is a sample file. You can see the array how it is assigned, could you please paste it as I have shown in the excel?
Thanks
BK
If you're trying to return the 7x2 array to 2x7 range then you will need to transpose the Array and invert the Ubounds, eg:
I am presuming this is a hypothetical scenario given the way the Array is being populated...![]()
Range("A2").Resize(UBound(a, 2), UBound(a, 1)).Value = Application.Transpose(a)
(and again, the above is assuming Option Base 1 at all times re: a)
![]()
Sub Test() Dim a() ReDim a(1 To 7, 1 To 1) i = 1 ReDim Preserve a(1 To 7, 1 To i) a(1, i) = "A-1" a(2, i) = "B-1" a(3, i) = "C-1" a(4, i) = "D-1" a(5, i) = "E-1" a(6, i) = "F-1" a(7, i) = "G-1" i = i + 1 ReDim Preserve a(1 To 7, 1 To i) a(1, i) = "A-2" a(2, i) = "B-2" a(3, i) = "C-2" a(4, i) = "D-2" a(5, i) = "E-2" a(6, i) = "F-2" a(7, i) = "G-2" Cells(20, 1).Resize(7, i) = a End Sub
Thanks a lot, this is working as expected now.
One question though, what means that the code is assuming Option Base 1 at all times?
Thanks again
BK
see VBE Help re: Option Base
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks