Code:
Option Explicit
Sub VergeltungswaffeV1V2() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=36401
Dim Ar As Long, Em As Long
Let Em = Range("A" & Rows.Count).End(xlUp).Row
Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1
Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array )
For Ar = 1 To Em ' The main data rows range
Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest
Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it
Let V2(Ar)(0) = StrReverse(V2(Ar)(0)): V2(Ar)(1) = StrReverse(V2(Ar)(1)): V2(Ar)(2) = StrReverse(V2(Ar)(2)): V2(Ar)(3) = StrReverse(V2(Ar)(3)): V2(Ar)(4) = StrReverse(V2(Ar)(4)): V2(Ar)(5) = StrReverse(V2(Ar)(5)) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around
Next Ar
' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like.
Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string
Range("A1:H1").EntireColumn.AutoFit
End Sub
Sub VergeltungswaffeV1V2_()
Dim Ar As Long, Em As Long
Let Em = Range("A" & Rows.Count).End(xlUp).Row
Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1
Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array )
For Ar = 1 To Em ' The main data rows range
Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest
Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it
Let V2(Ar) = Array(StrReverse(V2(Ar)(0)), StrReverse(V2(Ar)(1)), StrReverse(V2(Ar)(2)), StrReverse(V2(Ar)(3)), StrReverse(V2(Ar)(4)), StrReverse(V2(Ar)(5))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around
Next Ar
' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like.
Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string
Range("A1:H1").EntireColumn.AutoFit
End Sub
Sub VergeltungswaffeV1V2__()
Dim Ar As Long, Em As Long
Let Em = Range("A" & Rows.Count).End(xlUp).Row
Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1
Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array )
For Ar = 1 To Em ' The main data rows range
Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest
Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it
Let V2(Ar) = Array(StrReverse(V2(Ar)(5)), StrReverse(V2(Ar)(4)), StrReverse(V2(Ar)(3)), StrReverse(V2(Ar)(2)), StrReverse(V2(Ar)(1)), StrReverse(V2(Ar)(0))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around
Next Ar
' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like.
Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(1, 2, 3, 4, 5, 6)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string
Range("A1:H1").EntireColumn.AutoFit
End Sub
Reply With Quote
Bookmarks