Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

Some extra notes for this main forum post:
http://www.eileenslounge.com/viewtopic.php?f=27&t=38331

This is a sample input,

_____ Workbook: Split- Copy.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B
2	1,2,3,4	t,y,u,m

Worksheet: Sheet2Original

This is what I want out

_____ Workbook: Split- Copy.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B
2	1	t
3	2	y
4	3	u
5	4	m

Worksheet: Sheet2


I want to do this sort of thing,

__ arrOut()= App.Index(arrIn(), Rws(), Clms())

The arrIn() in this case will be all the input data. Conveniently, we can join the two cell values with a comma then split all that by comma to get a single array, {1 2 3 4 t y u m }
Then we need the Rws() like this
1 1
1 1
1 1
1 1
and the Clms() like this
1 5
2 6
3 7
4 8

'  https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16639&viewfull=1#post16639
Sub SplitData4()
Dim Ws2 As Worksheet: Set Ws2 = ThisWorkbook.Worksheets.Item("Sheet2")
Dim strDta As String: Let strDta = Ws2.Range("A2").Value & "," & Ws2.Range("B2").Value
Dim arrIn() As String
 Let arrIn() = Split(strDta, ",", -1, vbBinaryCompare)
' Or
 arrIn() = Split(Range("A2").Value & "," & Range("B2").Value, ",")
Dim Rws() As Variant
 Let Rws() = Evaluate("=Row(1:4)/Row(1:4)*Column(A:B)/Column(A:B)")
 Let Rws() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)")
Dim Clms() As Variant
 Let Clms() = Evaluate("=Row(1:4)+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
 Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")

Dim arrOut() As Variant
 Let arrOut() = Application.Index(arrIn(), Rws(), Clms())

 Let Ws2.Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()

' Or
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), Rws(), Clms())
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")"))

 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")/Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
 
End Sub

Sub StantiallyBeautiful() '  http://www.eileenslounge.com/viewtopic.php?f=27&t=38331
 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")/Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")*Column(A:B)/Column(A:B)"), Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

---

In actual fact, we can simplify things a bit , since Intersexual interception theory tells us that if Excel is looking for the indicies of this form
A b
C d
E f
G h
, but we only give it
1
, then it will see this instead
1 1
1 1
1 1
1 1

So that means we can replace Rws() with just 1
So that all simplifies it a bit…

Sub SplitData4b()
Dim Ws2 As Worksheet: Set Ws2 = ThisWorkbook.Worksheets.Item("Sheet2")
Dim strDta As String: Let strDta = Ws2.Range("A2").Value & "," & Ws2.Range("B2").Value
Dim arrIn() As String
 Let arrIn() = Split(strDta, ",", -1, vbBinaryCompare)
' Or
 arrIn() = Split(Range("A2").Value & "," & Range("B2").Value, ",")
'Dim Rws() As Variant
' Let Rws() = Evaluate("=Row(1:4)/Row(1:4)*Column(A:B)/Column(A:B)")
' Let Rws() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")/Row(1:" & (UBound(arrIn()) + 1) / 2 & ")*Column(A:B)/Column(A:B)")
Dim Clms() As Variant
 Let Clms() = Evaluate("=Row(1:4)+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
 Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")

Dim arrOut() As Variant
' Let arrOut() = Application.Index(arrIn(), Rws(), Clms())
 Let arrOut() = Application.Index(arrIn(), 1, Clms())
 Let Ws2.Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()

' Or
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut()
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), 1, Clms())
' Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = Application.Index(arrIn(), 1, Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")"))

 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), 1, Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

Sub StantiallyBeautifulb() '  http://www.eileenslounge.com/viewtopic.php?f=27&t=38331
 Range("A2").Resize((UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2, 2).Value = Application.Index(Split(Range("A2").Value & "," & Range("B2").Value, ","), 1, Evaluate("=Row(1:" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(Split(Range("A2").Value & "," & Range("B2").Value, ",")) + 1) / 2 & ")"))
End Sub

[DocAElstein]

some more notes on it....


later.....

    '  http://services.runescape.com/m=forum/forums.ws?278,279,877,64690220
    'Msg = Msg & Choose(Color, "black", "blue", "darkgreen") & "]"
    Msg = Msg & Choose(Color, "blue", "blueviolet", "darkgreen") & "]" ' Custom Colors Wonk
    Msg = Msg & Text

'Converts the specified text with the VBE Color (Black, Blue or Green) using BB code
Private Function BBColor(ByVal Color As Long, ByVal Text As String) As String
    Dim Msg As String
    Msg = "[color="
    '  http://services.runescape.com/m=forum/forums.ws?278,279,877,64690220
    'Msg = Msg & Choose(Color, "black", "blue", "darkgreen") & "]"
    Msg = Msg & Choose(Color, "blue", "blueviolet", "darkgreen") & "]" ' Custom Colors Wonk
    Msg = Msg & Text
    Msg = Msg & ""
    BBColor = Msg
End Function[/color]

'Converts the specified text with the VBE Color (Black, Blue or Green) using BB code
Private Function BBColor(ByVal Color As Long, ByVal Text As String) As String
    Dim Msg As String
    Msg = "[color="
    '  http://services.runescape.com/m=forum/forums.ws?278,279,877,64690220
    'Msg = Msg & Choose(Color, "black", "blue", "darkgreen") & "]"
   ' Msg = Msg & Choose(Color, "blue", "blueviolet", "darkgreen") & "]" ' Custom Colors Wonk
    Msg = Msg & Choose(Color, "blue", "darkblue", "darkgreen") & "]" ' Custom Colors Wonk  Serszud2 July 2023
    Msg = Msg & Text
    Msg = Msg & ""
    BBColor = Msg
End Function
[/color]

Option Explicit
Sub SeeIfThisWorks3() ' https://eileenslounge.com/viewtopic....309137#p309137
Dim Rng As Range
Set Rng = Worksheets.Item("Sheet1").Range("A1:F4")
Let Rng.Value = Evaluate("=IF(" & Rng.Address & "="""","""",IF(ISNUMBER(" & Rng.Address & "),1*" & Rng.Address & "," & Rng.Address & "))")
End Sub

---

Msg = Msg & Choose(Color, "blue", "darkblue", "darkgreen") & "]" ' Custom Colors Wonk Serszud2 July 2023

Option Explicit
Sub SeeIfThisWorks3()   '   https://eileenslounge.com/viewtopic.php?p=309137#p309137
Dim Rng As Range
 Set Rng = Worksheets.Item("Sheet1").Range("A1:F4")
 Let Rng.Value = Evaluate("=IF(" & Rng.Address & "="""","""",IF(ISNUMBER(" & Rng.Address & "),1*" & Rng.Address & "," & Rng.Address & "))")
End Sub

[DocAElstein]

This is post https://excelfox.com/forum/showthrea...ll=1#post16643
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16643&viewfull=1#post16643
https://excelfox.com/forum/showthrea...ge54#post16643
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page54#post16643

---

Some more notes related to these posts
http://www.eileenslounge.com/viewtop...296482#p296482

---

Making it more flexible / dynamic, most for academic interest and aesthetic Pleasure

The code is flexible already in terms of the number of elements in each cell, ( but note The macro assumes the cells all have the same number of elements - like __ 1,2,3,4 __ t,y,u,m __ 5,6,7,8 __ a,b,c,d )
This post extends the flexibility to a dynamic number of cells used. ( While I was writing this, the OP asked for a mod to increase the used cells from 2 to 4, so a flexible solution is , as often, worthwhile )

We almost certainly need to know how many cells we have, and the usual way is done to get that,
__ Lc = Ws1.Cells(2, Ws1.Columns.Count).End(xlToLeft).Column

Join an array of the cells
The key to the new flexible solution is to Join the elements of an array with a comma, where the elements are the cell values, which themselves are separated already with a comma. (So then as previously, we then finally have a single string of comma separated values, which we , as previously split by a comma, to give us our single array of all data values.
Initially we can get an array of cell values from applying the .Value property to our multi cell range.
Something of this sort of form, for example, for if we had 4 cells in the second row,
_________arrCels2D1Row() = Ws1.Range("A2:D2").Value2
A small snag here is that the array returned by the .Value property, is a 1 row, 2 Dimensional array, ( a pseudo “horizontal” , “single width” array) but the VBA strings Join function only accepts a 1 dimensional array. However, it’s a strange characteristic of VBA that many things if they are asked to return something in the orientation of pseudo “horizontal” , “single width” , then they return a 1 Dimensional array: It seems that somehow the internal workings often relate a row orientation to a single dimensional array. ( This is convenient to think about, as is the idea of pseudo “horizontal” , “single width” , since in the case of a 1 dimensional array we often write it in a line like {1, 2, 3, 6, "z"} , but we should remember that strictly speaking academically orientation in arrays is subjective. )
As example this seemingly redundant code line takes the first row from our single row 2 Dimensional array: Seemingly useless, but in fact it returns the 1 Dimensional array of cell values, as we require, -
___ arrCels1D() = Application.Index(arrCels2D1Row(), 1, 0)
___ ___ - Effectively that converts a 2 Dimensional 1 row array into a 1 Dimensional array, and why VBA has that sort of strange characteristic thing is not clearly known


After this, we simply modify the previous solutions to replace some hard coded values with the dynamic Lc: For example we see the number 2 used frequently, when we originally had 2 cells, and this will likely need to be replaced by Lc, and correspondingly we used B in places where this will likely need to be replaced by the column letter corresponding to column Lc


Unfortunately, the final single code line does not quite fit on one line, but it is one code line, but needs to be split to get it in the VB Editor

Sub PrettydammBeautiful()
 Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = _
 Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))

End Sub

Here the full story: see next post

[DocAElstein]

Here is the full workings for the last macro from the last post

Sub SplitDataFlexibly() '
Rem 1 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Lc As Long: Let Lc = Ws1.Cells(2, Ws1.Columns.Count).End(xlToLeft).Column: Lc = Cells(2, Columns.Count).End(xlToLeft).Column
Rem 2 create a 1 Dimensional array of all data
Dim LCL As String: Let LCL = Split(Cells(1, Lc).Address, "$", 3, vbBinaryCompare)(1): LCL = Split(Cells(1, Lc).Address, "$")(1) ' what we are doing is splitting like  $D$1  by the  $  and then taking the second element,  in the example that will be  D
Dim arrCels2D1Row() As Variant: Let arrCels2D1Row() = Ws1.Range("A2:" & LCL & "2").Value2
Dim arrCels1D() As Variant: Let arrCels1D() = Application.Index(arrCels2D1Row(), 1, 0)
Dim strDta As String: Let strDta = Join(arrCels1D(), ",")                                        'Ws2.Range("A2").Value & "," & Ws2.Range("B2").Value
Rem 3 Making previous solution dynamic, - requires changing  B  with  " & LCL & "   and some hard coded occurasnces  of  2  with   Lc
Dim arrIn() As String
 Let arrIn() = Split(strDta, ",", -1, vbBinaryCompare)
' Or
 arrIn() = Split(Join(arrCels1D(), ","), ",")
Dim Clms() As Variant
 ' the next lines, used in previous example. is for the case of two cells, so we need to change some hard coded stuff to make the solution dynamic. ' Let Clms() = Evaluate("=Row(1:4)+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
 ' Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / 2 & ")+((Column(A:B)-1)*" & (UBound(arrIn()) + 1) / 2 & ")")
  Let Clms() = Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(arrIn()) + 1) / Lc & ")")
Dim arrOut() As Variant
 Let arrOut() = Application.Index(arrIn(), 1, Clms())
' Let Ws2.Range("A2").Resize((UBound(arrIn()) + 1) / 2, 2).Value = arrOut() ' This was the case for 2 cells
 Let Ws1.Range("A2").Resize((UBound(arrIn()) + 1) / Lc, Lc).Value = arrOut()
' Or
' Range("A2").Resize((UBound(arrIn()) + 1) / Lc, Lc).Value = arrOut()
' Range("A2").Resize((UBound(arrIn()) + 1) / Lc, Lc).Value = Application.Index(arrIn(), 1, Clms())
' Range("A2").Resize((UBound(arrIn()) + 1) / Lc, Lc).Value = Application.Index(arrIn(), 1, Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(arrIn()) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(arrCels1D(), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(arrCels1D(), ","), ","), 1, Evaluate("=Row(1:" & (UBound(arrIn()) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(arrIn()) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(arrCels1D(), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(arrCels1D(), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(arrCels1D(), ","), ",")) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(Split(Join(arrCels1D(), ","), ",")) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Ws1.Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(Application.Index(Ws1.Range("A2:" & LCL & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Ws1.Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Lc & ")+((Column(A:" & LCL & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Ws1.Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(Application.Index(Ws1.Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Ws1.Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Lc & ")+((Column(A:" & Split(Cells(1, Lc).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Lc, Lc).Value = Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Lc & ")+((Column(A:" & Split(Cells(1, Lc).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Lc & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(arrCels2D1Row(), 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(Range("A2:" & LCL & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))
' Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Lc).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))
 Range("A2").Resize((UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column, Cells(2, Columns.Count).End(xlToLeft).Column).Value = _
 Application.Index(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ","), 1, Evaluate("=Row(1:" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")+((Column(A:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & ")-1)*" & (UBound(Split(Join(Application.Index(Range("A2:" & Split(Cells(1, Cells(2, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & "2").Value2, 1, 0), ","), ",")) + 1) / Cells(2, Columns.Count).End(xlToLeft).Column & ")"))

End Sub

[DocAElstein]

In support of this main Forum post:
http://www.eileenslounge.com/viewtop...297074#p297074 http://http://www.eileenslounge.com/viewtop...297074#p297074
First overcomplicated Solution

Hello

.... in real these values will go to another WBK.
:::
or now just one question, "public" will always declare for userfrom means storing value either within WBK or for Other WBK.

Public means that the variable will be "known" in all code modules of the same workbook, but not in code modules in other open workbooks. .

If Public variables are being the things that go in a normal code module, as I think they are, technically, or officially, or words to that effect, then that is the case that they won’t be known in other workbooks.
In other words, for the purposes of what is going on here, it means you will need to be storing them in the same workbook, as Hans said, and how he demonstrated.

However, you can do something that technically is not involving Public variables, but as far as I can tell, to all intents and purposes, is in effect the same thing as if you could have those Public variable in a different workbook.

The short story is:
Instead of putting the two public variables in a standard normal code module, ( in the same workbook) as Hans did, we can put them in any Class object code module in any open workbook. Technically they are not called Public variables. They are , I think, properties of the instantiated Class object, and we can access them, in the usual way that we access properties of an object.

The full story
PurseWayDoughPublicVariables.xls
I have another workbook uploaded, PurseWayDoughPublicVariables.xls . That is just to hold these variables. (I will call them “pseudo” Public variables, just because I feel like it ),
I can put them in any Class object code module, but just for fun, I will put C1 in a worksheet code module, and C2 in the ThisWorkbook code module.

So, this is what Hans did, public variables in a standard normal module like
Standard module, Module1

 Public C1 As String
Public C2 As String

Instead of doing that , I will put those variables in Class object code modules in PurseWayDoughPublicVariables.xls, like this:

Worksheet code module, Sheet1

 Public C1 As String
'
'
'
Sub PhilC1(ByVal Wrd As String)
 Let C1 = Wrd
End Sub

Workbook code module, ThisWorkbook

 Public C2 As String
'
'
'
Sub PhilC2(ByVal Wrd As String)
 Let C2 = Wrd
End Sub

*** The reason for those extra macros that fill the variables will be apparent shortly….
_.__________________________________-

Sample for Eli.xlsm
I need to modify now the workbook uploaded by Hans, in 3 main ways:
_(i) I don’t need the two public variables in a standard normal code module anymore
_(ii) I need to modify slightly how I reference the variables

 Sub Fi_l()
    'Act_ive
 'Let Range("A2").Resize(10).Value = C1
 Let Range("A2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").Worksheets("Sheet1").C1
 'let Range("B2").Resize(10).Value = C2
 Let Range("B2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").C2
End Sub

_(iii) Filling the variable is slightly more tricky. As far as I know, I can’t easily directly fill them from a macro in Sample for Eli.xlsm. - ***Edit: not true - see next post!! But I can run those extra macros*** that fill the variables, from Sample for Eli.xlsm
So to do that I modify the coding in the UserForm thus, ( for the purposes of this demo, I assume the two workbooks are stored in the same place):

 Private Sub CommandButton1_Click()
    Select Case Me.CheckBox1
     Case True
      'C1 = "yes"
      Application.Run Macro:="'" & ThisWorkbook.Path & "\" & "PurseWayDoughPublicVariables.xls'!Sheet1.PhilC1", Arg1:="Yus"
    End Select
    Select Case Me.CheckBox2
     Case True
      'C2 = "yes"
      Application.Run Macro:="'" & ThisWorkbook.Path & "\" & "PurseWayDoughPublicVariables.xls'!ThisWorkbook.PhilC2", Arg1:="Ja"
    End Select
 Unload Me
 Call Sheet2.Fi_l
End Sub

_.____

That’s it. So download both files, store them in the same place, and then the coding in Sample for Eli.xlsm should work as before. The only difference is that you are using the “pseudo” public variables in the workbook PurseWayDoughPublicVariables.xls

_.________________________________________________ _____________________


I have not seen this use of “pseudo” public variables much before, so there may be some reason I don’t know about why they should not be used??
But I use them myself sometimes, and so far I have never seen them behave any differently to “proper” public variables

( I would just finally say that I don’t use public variables much myself, pseudo or otherwise, if I can find another way to do what I want. I don’t like public variables myself. For one reason: I find they have an annoying habit of getting emptied sometimes. )



Alan


Ref
https://stackoverflow.com/questions/...ther-workbook#
https://excelfox.com/forum/showthrea...ll=1#post11870
https://stackoverflow.com/questions/...12342#59812342
https://www.mrexcel.com/board/thread.../#post-4629654

[DocAElstein]

In support of this main Forum post:
http://www.eileenslounge.com/viewtop...297074#p297074 http://http://www.eileenslounge.com/viewtop...297074#p297074
Second simplified Solution

I think in the first solution I made initially a mistake in trying to set the pseudo public variables, *** and so went off in a tangent using the Application.Run stuff. You don’t need any of that and can forget the two macros that fill the variables as well.

You just need this

Worksheet code module, Sheet1 ( in PurseWayDoughPublicVariables.xls )

 Public C1 As String

Workbook code module, ThisWorkbook ( in PurseWayDoughPublicVariables.xls )

 Public C2 As String

And then the other macros are like

Private Sub CommandButton1_Click()
    Select Case Me.CheckBox1
     Case True
      'C1 = "yes"
                                          '  Application.Run Macro:="'" & ThisWorkbook.Path & "\" & "PurseWayDoughPublicVariables.xls'!Sheet1.PhilC1", Arg1:="Yus"
     Let Workbooks("PurseWayDoughPublicVariables.xls").Worksheets("Sheet1").C1 = "Yus"
    End Select
    Select Case Me.CheckBox2
     Case True
      'C2 = "yes"
                                          '  Application.Run Macro:="'" & ThisWorkbook.Path & "\" & "PurseWayDoughPublicVariables.xls'!ThisWorkbook.PhilC2", Arg1:="Ja"
     Let Workbooks("PurseWayDoughPublicVariables.xls").C2 = "Ja"
    End Select
 Unload Me
 Call Sheet2.Fi_l
End Sub

Sub Fi_l()
    'Act_ive
 'Let Range("A2").Resize(10).Value = C1
 Let Range("A2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").Worksheets("Sheet1").C1
 'let Range("B2").Resize(10).Value = C2
 Let Range("B2").Resize(10).Value = Workbooks("PurseWayDoughPublicVariables.xls").C2
End Sub

[DocAElstein]

Some extra notes for this Thread:
http://www.eileenslounge.com/viewtopic.php?f=30&t=38460

Hans Solution http://www.eileenslounge.com/viewtop...297266#p297266
This is a nice solution which I totally misread, or rather in my ignorance, I did not understand.

The main point I missed is…
The solution assumes that the final solution actually has a 26 element 1 dimensional array, and the weight numbers in that array are sorted in alphabetical order, so that the first element represents the weight for “A” and the last Element represents the weight for “Z”, etc.
( So the array Letters() is redundant, and only the Weights() array is needed )
Hans has kindly set me straight and explained where I was going wrong. The final working version of his solution is

Sub Testit()
 MsgBox prompt:=Weight("ZAC")
End Sub
' https://eileenslounge.com/viewtopic.php?f=30&t=38460&sid=4295ec4560088f42492ca29590271a87
Public Function Weight(S As String) As Long ' http://www.eileenslounge.com/viewtopic.php?p=297266#p297266
Dim Weights() As Variant  ' Letters() As Variant,
Dim i As Long
'    Letters = Array("A", "B", "C", ..., "Z")
'    Weights = Array(1, 5, 3, ..., 2)
 '                     A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2) ' Watch : - : Weights() :  : Variant/Variant(0 to 25) : Module1.Weight
    For i = 1 To Len(S)
     Let Weight = Weight + Weights(Asc(Mid(S, i, 1)) - 65)
    Next i
End Function

How is that working:
We are looping through each character, then doing something clever to get the running total. The clever bit is getting the array element
To demonstrate that working consider a couple of examples for the case of a word having an A and a Z in it
A has the Ascii Code number of 65. So we end up referring to Weights(65-65) = Weights(0) , which is the first element typically in a 1 dimensional array that starts at indicia 0
Z has the Ascii Code number of 90. So we end up referring to Weights(90-65) = Weights(25) , which is the last element in a 1 dimensional array of 26 elements that starts at indicia 0



In order for the function to get correct results in the case of lower case letters, then one way to do it, ( assuming you have the correct Weights() array you want for lower case letters), you would need to change the 65 to 97

Sub Testit()
Debug.Print Tab(4); "ASCII"; Tab(12); "Weight"
Debug.Print Tab(4); "Code"
 Call Weight("ZAC")
Debug.Print
 Call WeightLowerCase("zac")
End Sub
' https://eileenslounge.com/viewtopic.php?f=30&t=38460&sid=4295ec4560088f42492ca29590271a87
Public Function Weight(S As String) As Long ' http://www.eileenslounge.com/viewtopic.php?p=297266#p297266
Dim Weights() As Variant  ' Letters() As Variant,
Dim i As Long
 '                     A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2) ' Watch : - : Weights() :  : Variant/Variant(0 to 25) : Module1.Weight
    For i = 1 To Len(S)
     Let Weight = Weight + Weights(Asc(Mid(S, i, 1)) - 65)
     Debug.Print Mid(S, i, 1); Tab(4); Asc(Mid(S, i, 1)); Tab(8); Asc(Mid(S, i, 1)) - 65; Tab(12); Weights(Asc(Mid(S, i, 1)) - 65)
    Next i
End Function
Public Function WeightLowerCase(S As String) As Long ' http://www.eileenslounge.com/viewtopic.php?p=297266#p297266
Dim Weights() As Variant  ' Letters() As Variant,
Dim i As Long
 '                     a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2) '
    For i = 1 To Len(S)
     Let WeightLowerCase = WeightLowerCase + Weights(Asc(Mid(S, i, 1)) - 97)
     Debug.Print Mid(S, i, 1) & vbTab & Asc(Mid(S, i, 1)) & vbTab & Asc(Mid(S, i, 1)) - 97 & vbTab & Weights(Asc(Mid(S, i, 1)) - 97)
    Next i
End Function

Here is the Debug.Print output from the last demo coding

   ASCII   Weight
   Code
Z   90  25  2 
A   65  0   1 
C   67  2   3 

z   122 25  2
a   97  0   1
c   99  2   3

[DocAElstein]

Here is an alternative single liner ( almost ## ) type solution to the last post. It was much simpler than I expected, and ends up much shorter than these solutions of mine usually do. (## There was a small snag, not solved yet, which means I have to do it in 2 code lines for now. I may take a look at that later here: https://excelfox.com/forum/showthrea...ll=1#post16655 )

Solution explanation.
Part 1. Background
This is all to do with
_ “my”** ____arrOut()=Index(ArrIn(), Rws(), Clms()) ______ type solutions, ( https://www.excelforum.com/excel-new...ml#post4571172 )
and also
_ using the Match in a similar way – ( some time ago I obsessed with trying out Application.Match where the first argument is an array, in a similar way to those of those array arguments Rws() and Clms() in Index. I got so obsessed I littered a sub forum with over long posts until they deleted them all and limited the post size to stop me doing it again. With hindsight, not a bad thing to do, as I could not see the wood for the trees back then. I can now, and its not at all difficult to understand, so I really don’t need all that crap anymore. Let me call that for now “my” **
________arrOut() = Match(arrArg1(), arrIn() , 0 ) ___ type solution.
( ** I use the word “my” lightly. – I learnt all this stuff from looking at stuff from Rick Rothstein and snb. ( I am not sure if they “invented it” , or got it from other peoples stuff. if I added anything “new” , it might be some of my detailed explanations, which whilst I don’t know if they are correct, they seem to be a valid theory as they go a long way to explain the results ) )


Here is a quick demo of how
_ my ____arrOut()=Match(arrArg1(), arrIn() , 0 ) ____ works
Ordinarily, or most usually the first argument is just one thing that you are looking for. As far as I know all documentation tells you that the way Match in Excel works is, ( simplified ) :
_... you look in the second argument array of things for the thing in the first argument, and , assuming you find it, return the position along where it is, pseudo like
_____ Match( b , { a, b, c } , 0 ) = 2
In the practice we sometimes, ( not always ) , find that things in Excel will work with array arguments and return a corresponding array of outputs. So taking that last example, pseudo like
_____ Match( {b, a} , { a, b, c } , 0 ) = {2, 1}

So that is a bit of theory out of the way. ( I have done a fuller explanation in a few places of how the Application.Index with Look Up Rows and Columns Arguments as VBA Arrays works in a few places
https://excelfox.com/forum/showthrea...ll=1#post16455
https://www.excelforum.com/excel-new...ml#post4571172 )

---

Part 2. Here is my solution examples
Refering to the first long macro below:

Rem1 is just making some stuff I need for the demo. I use the string example of “ZAC” as per the original OP example http://www.eileenslounge.com/viewtopic.php?f=30&t=38460 . For reasons given in the next bit, I make an array of the 26 Ascii Code numbers for the capital alphabet characters, A, B. C ….Z , Asskeys() = { 65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81 ,82,83,84,85,86,87,88,89,90 }
My array of the weights values, Weights(), for the characters will be the same size as Asskeys() and will have the corresponding weight value for each of the 26 characters in the same order.
Once again it will be clear why later. For now, the point is to have arrays of the same size with related things in the same order

 ' '   Ascii Code       65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
' '                     A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
' Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)  

Rem 2
I found a way on the internet to turn my string example into an array of single characters, which is what I will be feeding into my Match as first argument. ( Unfortunately it does not return in each element the character, but rather its Ascii Code. But for my purposes that’s just as good.

Rem 3 Match
This is the Match bit, and it tells me the position along where I find the three Ascii Code numbers of “ZAC” in the Ascii Code array, Asskeys()
We get from match here, a 3 element array, MtchRes(), of the position along, of the characters in “ZAC” in the array Asskeys(). We have organised that the array of weights is organised in the same order, so this will also be the position along of the corresponding weight number in the array of weights, Weights().
In the example we should have then an array like {26, 1, 3} _ ( if you have followed the logic so far, you can see this is like a pseudo Alphabet position of the characters, Z , A , and C __ (But don’t get confused with Ascii codes, which is pseudo like the official position of characters, and defined by some world standard, that Excel knows about. As example, capital A is listed as Ascii code 65, lowercase a is listed as 97 )

Rem 4 Index
The 3 element array of the position along, of the characters in “ZAC” in the array Asskeys(), is effectively the Clms() array we need for a __arrOut()=Index(ArrIn(), Rws(), Clms())__type solution, where the look up array, arrIn() , will be the weights array, Weights()
The returned array from Index , arrOut(), will be an array, of 3 numbers, which are the weight numbers for the example string “ZAC”.

Rem 5
Finally we simply sum the elements of the found weight values, as per the original OP request.

Sub AssKeys()
Rem 1 Make the arrays and other hard coded things for the demo
Dim AssKeys(1 To 26) As Long
Dim Eye As Long
    For Eye = 65 To 90 Step 1
     Let AssKeys(Eye - 64) = Eye
    Next Eye
' OR
'  Dim AssKeys() As Variant: Let AssKey() = Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
Dim Weights() As Variant:
 '   Ascii Code       65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
 '                     A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
 Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)
Dim ZAC As String
 Let ZAC = "ZAC" ' This is a demo example text string
Rem 2 String to array
Dim arrZAC() As Byte: Let arrZAC() = StrConv(ZAC, vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Rem 3 Match
Dim MtchRes() As Variant
 Let MtchRes() = Application.Match(arrZAC(), AssKeys(), 0)
Rem 4 Index
Dim arrOut() As Variant
 Let arrOut() = Application.Index(Weights(), 1, MtchRes())
Rem 5
Dim Some As Long: Let Some = Application.Sum(arrOut())
End Sub

Here the shortening possibilities

Sub BeautifulAsskeys()
Rem 1 Make the arrays and other hard coded things for the demo
'Dim Asskeys(1 To 26) As Long
'Dim Eye As Long
'    For Eye = 65 To 90 Step 1
'     Let Asskeys(Eye - 64) = Eye
'    Next Eye
' OR
'  Dim AssKeys() As Variant: Let AssKey() = Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90)
'Dim Weights() As Variant:
' '   Ascii Code       65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90
' '                     A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z
' Let Weights() = Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2)
'Dim ZAC As String
' Let ZAC = "ZAC" ' This is a demo example text string
Rem 2 String to array
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Rem 3 Match
'Dim MtchRes() As Variant
' Let MtchRes() = Application.Match(arrZAC(), Asskeys(), 0)
' Let MtchRes() = Application.Match(StrConv(ZAC, vbFromUnicode), Asskeys(), 0)' this does not work
Rem 4 Index
'Dim arrOut() As Variant
' Let arrOut() = Application.Index(Weights(), 1, MtchRes())
Rem 5
Dim Some As Long: Let Some = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Sub
'
Sub AsKeys()                                                       '  http://www.eileenslounge.com/viewtopic.php?p=297288#p297288
Dim arrZAC() As Byte: Let arrZAC() = StrConv("ZAC", vbFromUnicode) ' https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters
Dim Some As Long: Let Some = Application.Sum(Application.Index(Array(1, 5, 3, 1, 4, 3, 2, 1, 6, 4, 5, 3, 2, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 2), 1, Application.Match(arrZAC(), Array(65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90), 0)))
End Sub

---

** I use the word “my” lightly. – I learnt all this stuff from looking at stuff from Rick Rothstein and snb. ( I am not sure if they “invented it” , or got it from other peoples stuff. if I added anything “new” , it might be some of my detailed explanations, which whilst I don’t know if they are correct, they seem to be a valid theory as they go a long way to explain the results

[DocAElstein]

Spare post for sharing some documents
Ersatzpost für die gemeinsame Nutzung einiger Dokumente


Attachment 3932


Attachment 3933


Attachment 3934

---

AlanSdattHof15Aug2022.docx
https://app.box.com/s/xxntrig04ppe7hdd68se882rm3fv2ysw https://bit.ly/3QXSEv0

AlanSdattHof15Aug2022.doc
https://app.box.com/s/pt4v46nl28qya2bfycgq2rdfz3a35fq8 https://bit.ly/3STVvqm





This post links
AlanSdattHof15Aug2022.docx
AlanSdattHof15Aug2022.doc
https://excelfox.com/forum/showthrea...ll=1#post16655
https://excelfox.com/forum/showthrea...ge55#post16655
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16655&viewfull=1#post16655
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page55#post16655
https://bit.ly/3AEPlmM https://bit.ly/3wmjQvh

[DocAElstein]

In support of this main forum post
https://excelfox.com/forum/showthrea...-Excel-Formula

Assuming this is data and wanted results from column D

_____ Workbook: DogsNutsFormulas.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Code	Values		Code	Value1	Value2	Value3	Value4
2	1001	2101		1001	2101	5205	2605	9285
3	1001	5205		2604	4256	7458	3555
4	1001	2605
5	1001	9285
6	2604	4256
7	2604	7458
8	2604	3555
9

Worksheet: SimpleUniqueVLookUp



Enter this In D2 via CSE, then drag it down:
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")

Row\Col	D
2	=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)),"")
3	=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)),"")

Alternative formula for earlier versions of Excel:
=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))

Row\Col	D
2	=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$8),0)))
3	=IF(ISERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0))),"",INDEX($A$2:$A$8,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$8),0)))

Hi Amelynn

…..

• If after TextBox(n) it is necessary to put the .text or .value
• If not putting anything is wrong (although it works)
• What is the difference between the three things……

I can’t answer most of your question here, unfortunately, because
_ I have no experience with Textboxes
_ I am not too familiar with the .Text property.

I can only tell you the small part that I know about: - what I know about is
Range__
Range__.Value
Range__.Value2

Range__ object
Range__ is an object with an extremely large number of properties, methods and various things.
Range__ is all to do with how Excel organises and uses cells. Understanding the Range__ object is probably one of the most important things to know about in Excel and VBA, especially if you are interested in spreadsheet things.
But many times, we are only interested in the value that is in a cell. Because most people are often only interested in the value in a cell, Microsoft have made .Value the default of what you get if you just use Range__. So most of the time if you choose to write just Range__ , then in fact , Excel will not see that, instead it will see and use Range__.Value
It is just personal choice if you choose to use Range__.Value or Range__. Usually there are no problems if you just use Range__ , but I have seen occasions when this caused problems as there may be occasions when Excel tries to refer to the Range__ object instead of the value.
So personally I prefer to always include the .Value if I am interested in a value. I will only leave out the .Value if I am doing something that wants me to reference the Range__ object. Just personal choice.

So, in your example, when you used Worksheets("Sheet1").Range("B10") , Excel did not see and use that.
Instead, Excel saw and used this: Worksheets("Sheet1").Range("B10").Value

....After the range, should I put .text, .value or just nothing? ...

So in your examples you could probably just use nothing , but I personally would recommend that you include .Value ( or .Value2 )
But that is just my personally recommendation

.Value or .Value2
.Value is almost the simplest cell value. But not quite. If you are interested in dates or currency, then .Value will show you the date or currency in a date or currency format.
.Value2 is the most simplest cell value as Excel has it held before any formatting is done.

Personally I will use .Value2 most of the time, because it may work a little faster or may be less likely to problems caused by awkward cell formatting issues. I think theoretically it is also a bit more efficient to use .Value2

So….

.....If not putting anything is wrong (although it works).....

It is not wrong to put nothing. But it is bad practice, as it may cause problems in other situations in Excel VBA
(More than half of people put nothing, and they will often get a problem later that they don’t understand )

---

....Should I necessarily declare that, for example, "Niebla" is a variable of type Str?

The way that you are using "Niebla" in VBA coding is perfectly alright, because: Most of the time in VBA coding, if VBA sees something enclosed in quotes, _ "__" _ , like
"xyz"
, then VBA will take the value of _ xyz _ to be a string.
Even if , in your coding, you did this
"3"
, the VBA would not take the FONT=Courier New]"3"[/FONT] as a number. It would see it as a string, just as it would see this as a string
"I have 3 Apples"


Note that VBA is very user friendly with numbers and strings. For example if you pass it a string like "3" in a function wanting a number, the VBA will not error, but instead it will take a number 3 instead.

In many other computer languages you must be much more careful in defining precisely variable types.

---

That is as close as I can come to answering your question.
But I do know about Range__ , Range__.Value , Range__.Value2 quite well.
So I am happy to give you any further clarity on those things. Those things are all to do with range referencing in Excel and VBA, which is a very important thing to know about.


Alan

---

Ref
https://fastexcel.wordpress.com/2011...w-to-avoid-it/