HI Yasser,
_... ah yes – a silly mistake on my behalf.. if you take a look at all the
Index bits, you can see that I forgot to change the hard coded column index from
3 to the variable for the column number
Clm
So everywhere, something like this:
Index(Rng, ______________, 3 )
, should be
Index(Rng, ______________, Clm )
Here you go:
Code: Select all
Sub TestVeeV() ' http://www.eileenslounge.com/viewtopic.php?p=305791#p305791
Dim VeeV() As Variant
Let VeeV() = GetArraysIndxEP(Range("C6:F11"), 3)
Let VeeV() = GetArraysIndx(Range("C6:F11"), 4)
End Sub
Public Function GetArraysIndx(ByVal Rng As Range, ByVal Clm As Long) As Variant
Let GetArraysIndx = Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm)
End Function
Public Function GetArraysIndxEP(ByVal Rng As Range, ByVal Clm As Long) As Variant
Dim arrValues() As Variant
Let arrValues() = Application.Index(Rng, Array(1, 2, 3, 4, 5, 6), Array(3, 3, 3, 3, 3, 3))
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:F)"), Array(3, 3, 3, 3, 3, 3))
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:F)"), 3) ' Excel VBA Interception and Implicit Intersection Theory tells us that the extended range help matrix will be full with 3 https://excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp
Dim CL As String: Let CL = Split(Cells(1, 6).Address, "$", -1, vbBinaryCompare)(1) ' Split on example $E$1 will give like "" "E" "1" I want the second one which is the index of 1 because split in default setings is in base 0 so like in this 3 element example is like 0 1 2
Let CL = Split(Cells(1, Rng.Rows.Count).Address, "$", -1, vbBinaryCompare)(1)
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:" & CL & ")"), 3)
' or
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), 3)
' remember finally to change the hard coded column index
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm)
Let GetArraysIndxEP = arrValues()
End Function
Code: Select all
Sub TestVee() ' http://www.eileenslounge.com/viewtopic.php?p=305791#p305791
Dim Vee() As String
Let Vee() = GetArraysIndxEP2(Range("C6:F11"), 4)
Let Vee() = GetArraysIndx2(Range("C6:F11"), 3)
End Sub
Public Function GetArraysIndx2(ByVal Rng As Range, ByVal Clm As Long) As Variant
Let GetArraysIndx2 = Split(Application.Trim(Join(Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm), " ")), " ")
End Function
Public Function GetArraysIndxEP2(ByVal Rng As Range, ByVal Clm As Long) As Variant
Dim arrValues() As Variant
Let arrValues() = Application.Index(Rng, Array(1, 2, 3, 4, 5, 6), Array(3, 3, 3, 3, 3, 3))
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:F)"), Array(3, 3, 3, 3, 3, 3))
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:F)"), 3) ' Excel VBA Interception and Implicit Intersection Theory tells us that the extended range help matrix will be full with 3 https://excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp
Dim CL As String: Let CL = Split(Cells(1, 6).Address, "$", -1, vbBinaryCompare)(1) ' Split on example $E$1 will give like "" "E" "1" I want the second one which is the index of 1 because split in default setings is in base 0 so like in this 3 element example is like 0 1 2
Let CL = Split(Cells(1, Rng.Rows.Count).Address, "$", -1, vbBinaryCompare)(1)
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:" & CL & ")"), 3)
' or
Let arrValues() = Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), 3)
Dim strClm As String
Let strClm = Join(arrValues(), " ")
Let strClm = Application.Trim(strClm) ' http://www.eileenslounge.com/viewtopic.php?p=301761#p301761
Dim strarrValues() As String
Let strarrValues() = Split(strClm, " ", -1, vbBinaryCompare)
' or
Let strarrValues() = Split(Application.Trim(Join(Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), 3), " ")), " ")
' finally remember to change the hard coded colum to the column variable
Let strarrValues() = Split(Application.Trim(Join(Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm), " ")), " ")
Let GetArraysIndxEP2 = strarrValues()
End Function
_.______________________________________________________________________________________________________________________________
One other thing. If you prefer the second macros to return
Variant type elements instead of
String type elements , then you can play around with like, pseudo coding
VariantElementTypeArray = Index(StringElementTypeArray , 1, 0 )
So like this:
Code: Select all
Sub TestVeeVV() ' http://www.eileenslounge.com/viewtopic.php?p=305791#p305791
Dim Vee() As String
Let Vee() = GetArraysIndx22(Range("C6:F11"), 4)
Dim VeeV() As Variant
Let VeeV() = Application.Index(Vee(), 1, 0)
End Sub
Public Function GetArraysIndx22(ByVal Rng As Range, ByVal Clm As Long) As Variant
Let GetArraysIndx22 = Split(Application.Trim(Join(Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm), " ")), " ")
End Function
Or this to make it very beautiful
Code: Select all
Sub TestVee22V() ' https://www.eileenslounge.com/viewtopic.php?p=305940#p305940
Dim VeeV() As Variant
Let VeeV() = GetArraysIndx22V(Range("C6:F11"), 4)
End Sub
Public Function GetArraysIndx22V(ByVal Rng As Range, ByVal Clm As Long) As Variant
Let GetArraysIndx22V = Application.Index(Split(Application.Trim(Join(Application.Index(Rng, Evaluate("=Column(A:" & Split(Cells(1, Rng.Rows.Count).Address, "$")(1) & ")"), Clm), " ")), " "), 1, 0)
End Function