Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

[DocAElstein]

Some extra macros for this Thread
https://eileenslounge.com/viewtopic.php?f=27&t=36401
post
https://eileenslounge.com/viewtopic....282498#p282498

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

[DocAElstein]

An extra macro for this Thread
https://eileenslounge.com/viewtopic.php?f=27&t=36401
post
https://eileenslounge.com/viewtopic....282498#p282498

Option Explicit
Sub AL1AL2__() '
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 AL1 As Object, AL2 As Object: Set AL1 = CreateObject("System.Collections.ArrayList"): Set AL2 = CreateObject("System.Collections.ArrayList")
    For Ar = 1 To Em ' The main data rows range
     AL1.Add 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
     AL2.Add Split(StrReverse(AL1.Item(Ar - 1)(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 AL2.Item(Ar - 1) = Array(StrReverse(AL2.Item(Ar - 1)(5)), StrReverse(AL2.Item(Ar - 1)(4)), StrReverse(AL2.Item(Ar - 1)(3)), StrReverse(AL2.Item(Ar - 1)(2)), StrReverse(AL2.Item(Ar - 1)(1)), StrReverse(AL2.Item(Ar - 1)(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 in the Array Lists, one in each.  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(AL1.toarray(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1()
 Let Range("C2:H" & Em + 1 & "").Value = Application.Index(AL2.toarray(), 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