Convert array to 2D array suitable for populating in sheet

[YasserKhalil]

Hello everyone

I have this line of code

Code: Select all

a = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))

When I tried to populate such a line in worksheet, I encountered an error

Code: Select all

Range("A1").Resize(UBound(a, 1) + UBound(a, 2)).Value = a

I have looked at the locals window and found that the array is nested .. How can I populate such array or convert it to be 2D array?

[YasserKhalil]

I could use this approach

Code: Select all

a = [{"Header1","Header2";"Yasser",10;"Ahmed",20;"Reda",30}]
Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a

But I need to deal with the case of using the split array shown in the first post

[HansV]

How about writing a double loop?

[YasserKhalil]

No I don't want loops. I am avoiding loops as possible as I can.

[Doc.AElstein]

You have a 1 dimensional array of 2 one dimensional arrays
They have just one Ubound, not two, since they only have one dimension, not two

a is (0 to 1) – 2 elements , Ubound of 1
Each of the two elements is a 1 dimensional array of (0 To 3 ) – 4 elements, Ubound of 3

Yassers 1 dimensional array.jpg : https://imgur.com/5E5aBvd" onclick="window.open(this.href);return false;

Yassers 1 dimensional array.JPG

If you paste a 1 dimensional array to a worksheet, Excel treats it as if it is a 1 row, 2 dimensional array. So you actually should have a code line like
......Value.Resize(, Ubound(array))
or
......Value.Resize(1, Ubound(array))

Code: Select all

Sub Arrs()
Dim a
a = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
Range("A1").Resize(, UBound(a(0)) + 1).Value = a(0)
Range("A1").Offset(0, UBound(a(0)) + 1).Resize(, UBound(a(1)) + 1).Value = a(1)
End Sub

[YasserKhalil]

Thanks a lot Mr. Alan
Do you mean to populate into two columns I have to d such a code

Code: Select all

Dim a
a = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
Range("A1").Resize(UBound(a(0)) + 1).Value = Application.Transpose(a(0))
Range("B1").Resize(UBound(a(1)) + 1).Value = Application.Transpose(a(1))

Is there a way to convert the array to 2D array that is populated in one step instead of two ..? as there are about 20 columns not only one column .. so it is not logical in this case to use the same line 20 times

[Doc.AElstein]

I don’t know of anyway that does not involve looping to fill the two dimensional array

Edit - later I did ' https://eileenslounge.com/viewtopic.php ... 27#p266727
' https://eileenslounge.com/viewtopic.php ... 91#p266691

[YasserKhalil]

If there is no way except using loops. How would be looped in such array..? and how we call such nested arrays?
** As far as I know looping through arrays is faster than cells. But what if the array has over 1 million rows?

[HansV]

Why do you have such an array in the first place?

[YasserKhalil]

I am working on some information from a website that I need to handle in this way .. by splitting each block and then joining them as arrays.

[HansV]

Wouldn't it be easier to write the unsplit strings to the worksheet and to use TextToColumns to split the data in one go?

[YasserKhalil]

Populating the data into the worksheet after each block takes so much time .. for this reason, I am trying to do that in a different way by strong all in one array and at last, populate all in one shot

[Doc.AElstein]

How would be looped in such array..? and how we call such nested arrays?

Just like we already did to reference elements…like https://imgur.com/5E5aBvd" onclick="window.open(this.href);return false; , http://www.eileenslounge.com/viewtopic. ... 34#p266624" onclick="window.open(this.href);return false;
a( Aoutside )( Hinside ) - you see, we have arrays, in our array, a
You want to loop to get at this sort of thing,
a(0)(0) , a(1)(0)
a(0)(1) , a(1)(1)
a(0)(2) , a(1)(2)
a(0)(3) , a(1)(3)
Aoutside is two elements, 0 To 1 - a( 0 To 1 ) and
Hinside each of those is 4 elements, 0 To 3 - a(0)( 0 To 3 ) , a(1)( 0 To 3 )

Code: Select all

Sub HarraysInAourray() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=34371&p=266633#p266634
Dim Aout As Long, Hin As Long, a
a = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
Dim TooDee() As String: ReDim TooDee(1 To UBound(a(0)) + 1, 1 To UBound(a) + 1)
    For Aout = LBound(a) To UBound(a) 'we have a 2 element, 1 D array, 0 To 1, and...._
        For Hin = LBound(a(Aout)) To UBound(a(Aout))  '_... inside of each 2 elements is a 4 element ,1 D array, 0 To 3  -  a(0)( 0 To 3 ) , a(1)( 0 To 3 )
         'MsgBox prompt:=a(Aout)(Hin)
         Let TooDee(Hin + 1, Aout + 1) = a(Aout)(Hin)
        Next Hin
    Next Aout
Range("A1").Resize(UBound(TooDee(), 1), UBound(TooDee(), 2)).Value = TooDee()
End Sub

_.___________________________

But what if the array has over 1 million rows?

I can’t really say very accurately: That varies a lot depending on how many columns and also your computer, Excel version etc..

[YasserKhalil]

Thank you very much. Now it is solved.
Best Regards

[Doc.AElstein]

A “1 liner” solution, without looping..

I was not expecting this to work. It does. At least it works in Excel 2003, 2007 and 2010. It is possible that it might not work in more recent Excel versions***

I don’t have time to figure out why it works just now, ( but I will probably write a 30 page explanation in a few years, and post back. :) )
The Transpose will be the limitation. Transpose does not work very well at the best of times, and for large sizes it is full of bugs / limitations, ( http://excelmatters.com/2016/03/08/tran ... -and-2016/" onclick="window.open(this.href);return false; )
So it will be no good for Millions of rows, but for a few thousand it should be OK
( ***Actually, I expect the explanation for this working at all might help explain the bugs and limitations in Transpose )

Code: Select all

Sub HArraysInAourray2() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=34371#p266691
ActiveSheet.Cells.ClearContents
Dim a() As Variant, b() As Variant, Clms() As Variant
 Let a() = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
 Let b() = Application.Index(a(), Evaluate("=Row(1:2)"), Array(1, 2, 3, 4))
 Let b() = Application.Index(a(), Evaluate("=Row(1:2)"), Evaluate("=Column(A:D)"))
'  Index HarasyInAourasy.JPG :  https://imgur.com/fzb2GEo ,    https://imgur.com/p7QKdwU

Dim strEval As String
 Let strEval = "=Column(A:D)": Debug.Print strEval
 Let strEval = "=Column(A:" & Split(Cells(1, 4).Address, "$")(1) & ")": Debug.Print strEval
 Let Clms() = Evaluate(strEval): Let Clms() = Evaluate("=Column(A:" & Split(Cells(1, 4).Address, "$")(1) & ")")
 Let b() = Application.Index(a(), Evaluate("=Row(1:2)"), Clms())
 Let b() = Application.Index(a(), Evaluate("=Row(1:2)"), Evaluate("=Column(A:" & Split(Cells(1, 4).Address, "$")(1) & ")"))
 Let b() = Application.Index(a(), Evaluate("=Row(1:2)"), Evaluate("=Column(A:" & Split(Cells(1, UBound(a(0)) + 1).Address, "$")(1) & ")"))
 Let b() = Application.Index(a(), Evaluate("=Row(1:" & UBound(a()) + 1 & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(a(0)) + 1).Address, "$")(1) & ")"))

Range("A1").Resize(UBound(a()) + 1, UBound(a(0)) + 1).Value = b()
ActiveSheet.Cells.ClearContents
Range("A1").Resize(UBound(a(0)) + 1, UBound(a()) + 1).Value = Application.Transpose(b())
ActiveSheet.Cells.ClearContents
Range("A1").Resize(UBound(a(0)) + 1, UBound(a()) + 1).Value = Application.Transpose(Application.Index(a(), Evaluate("=Row(1:2)"), Evaluate("=Column(A:" & Split(Cells(1, UBound(a(0)) + 1).Address, "$")(1) & ")")))
End Sub

Code: Select all

Sub HArraysInAourray3()
Dim a
a = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
Range("A1").Resize(UBound(a(0)) + 1, UBound(a) + 1).Value = Application.Transpose(Application.Index(a, Evaluate("=Row(1:" & UBound(a) + 1 & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(a(0)) + 1).Address, "$")(1) & ")")))
End Sub

Index HarasyInAourasy.JPG : https://imgur.com/fzb2GEo" onclick="window.open(this.href);return false; , https://imgur.com/p7QKdwU" onclick="window.open(this.href);return false;
Ref
http://www.eileenslounge.com/viewtopic. ... 17#p265384" onclick="window.open(this.href);return false;
http://excelmatters.com/2016/03/08/tran ... -and-2016/" onclick="window.open(this.href);return false;
http://www.excelforum.com/development-t ... ost4213969" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... 1#post7214" onclick="window.open(this.href);return false;

[YasserKhalil]

Thank you very much. That's awesome
I have edited this part for the solution of One line solution so as to make it more flexible

Code: Select all

Evaluate("=Row(1:2)")

With

Code: Select all

Evaluate("=Row(1:" & UBound(a) + 1 & ")")

[Doc.AElstein]

correct, well spotted and well done ! ( I noticed that as well already and had already deleted the post and re posted before your post because of that! )

[YasserKhalil]

Thank you very much Mr. Alan
Best and Kind Regards

[Doc.AElstein]

Does this work in your Excel? ( What Excel version are you using? )

[YasserKhalil]

Yes, it works. I am working one Office 365 32 Bit