Convert array to 2D array suitable for populating in sheet

[Doc.AElstein]

Thanks

[Doc.AElstein]

I could ....= [{"Header1","Header2";"Yasser",10;"Ahmed",20;"Reda",30}]....

This reminded me of something.., or maybe just woke me up a bit I think…

We don’t need to mess about with Transpose.. forget all the transpose stuff… ( I was having a brain laps)…

Code: Select all

Sub HArraysInAourray4() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=34371&start=20
Dim a() As Variant, b() As Variant
ActiveSheet.Cells.ClearContents
 Let a() = Array(Split("Header1,Yasser,Ahmed,Reda", ","), Split("Header2,10,20,30", ","))
 Let b() = Application.Index(a(), [{1,2}], [{1;2;3;4}]):
 'Let b() = Application.Index(a, Evaluate("{1,2}"), Evaluate("{1;2;3;4}"))
 Let b() = Application.Index(a(), Array(1, 2), [{1;2;3;4}])
 Let b() = Application.Index(a(), Array(1, 2), Evaluate("=Row(1:4)"))
 Let b() = Application.Index(a(), Evaluate("=Column(A:B)"), Evaluate("=Row(1:4)"))
 Let b() = Application.Index(a(), Evaluate("=Column(A:B)"), Evaluate("=Row(1:4)"))
 Let b() = Application.Index(a(), Evaluate("=Column(A:B)"), Evaluate("=Row(1:4)"))
 Let b() = Application.Index(a(), Evaluate("=Column(A:" & Split(Cells(1, UBound(a()) + 1).Address, "$")(1) & ")"), Evaluate("=Row(1:4)"))
 Let b() = Application.Index(a(), Evaluate("=Column(A:" & Split(Cells(1, UBound(a()) + 1).Address, "$")(1) & ")"), Evaluate("=Row(1:" & UBound(a(0)) + 1 & ")"))
 Range("A1").Resize(UBound(a(0)) + 1, UBound(a()) + 1).Value = b()
ActiveSheet.Cells.ClearContents
 Range("A1").Resize(UBound(a(0)) + 1, UBound(a()) + 1).Value = Application.Index(a(), Evaluate("=Column(A:" & Split(Cells(1, UBound(a()) + 1).Address, "$")(1) & ")"), Evaluate("=Row(1:" & UBound(a(0)) + 1 & ")"))
Stop
End Sub

Code: Select all

Sub HArraysInAourray5()  ' One line solution (without transpose)
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.Index(a, Evaluate("=Column(A:" & Split(Cells(1, UBound(a) + 1).Address, "$")(1) & ")"), Evaluate("=Row(1:" & UBound(a(0)) + 1 & ")"))
End Sub

( So this may work a bit better as it is not limited by the Transpose bugs and limitations )
Alan

[YasserKhalil]

I am interested only in one line solution. is there a difference between this version of one line solution from the previous?

[Doc.AElstein]

it is shorter - it does not use Transpose. - it is simpler, and does not use Transpose, so is not limited by the Transpose bugs and limitations which I mentioned before. It should work a bit better, and should be less likely to have bugs or problems which often occur with Transpose

Excel VBA Transpose is not a good thing to use because it has bugs and limitations. ( I only used it in previous , because I did not think how to use it without it. But later I figured out how to do it without it)

[YasserKhalil]

Yes you are right. That would be better certainly. Thanks a lot for sharing your great ideas.