Convert array to 2D array suitable for populating in sheet
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Convert array to 2D array suitable for populating in she
Thanks
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
1D array to 2D array - One liner (without transpose)
This reminded me of something.., or maybe just woke me up a bit I think…YasserKhalil wrote:I could ....= [{"Header1","Header2";"Yasser",10;"Ahmed",20;"Reda",30}]....
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
Alan
Last edited by Doc.AElstein on 06 Apr 2020, 19:38, edited 3 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Convert array to 2D array suitable for populating in she
I am interested only in one line solution. is there a difference between this version of one line solution from the previous?
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Convert array to 2D array suitable for populating in she
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)
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)
Last edited by Doc.AElstein on 06 Apr 2020, 18:19, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Convert array to 2D array suitable for populating in she
Yes you are right. That would be better certainly. Thanks a lot for sharing your great ideas.