'le plus grand AVEC FONCTION MAX
Function maxInJoinTexteIncolumn(r As Range, Optional séparator As String = ",")
Dim Tbl(), T
T = Join(Application.Transpose(Application.index(r, 0, 1)), séparator)
T = Replace(T, séparator & séparator, séparator)
T = "{" & Replace(T, séparator, ",") & "}"
Tbl = Evaluate(T)
maxInJoinTexteIncolumn = Application.WorksheetFunction.Max(Array(Tbl()))
End Function
Sub test3()
MsgBox maxInJoinTexteIncolumn([A5:A7], ",")
MsgBox maxInJoinTexteIncolumn([A1:A3], "-")
End Sub
'*********************************************************************************
'le N eme plus grand avec fonction LARGE
Function maxInJoinTexteIncolumn2(r As Range, Optional séparator As String = ",", Optional index& = 1)
Dim Tbl(), T
T = Join(Application.Transpose(Application.index(r, 0, 1)), séparator)
T = "{" & Replace(T, séparator, ",") & "}"
Tbl = Evaluate(T)
maxInJoinTexteIncolumn2 = Application.WorksheetFunction.Large(Tbl, index)
End Function
Sub test5()
MsgBox maxInJoinTexteIncolumn2([A1:A3], "-", 1)
MsgBox maxInJoinTexteIncolumn2([A1:A3], "-", 3)
MsgBox maxInJoinTexteIncolumn2([A5:A7], ",", 1)
MsgBox maxInJoinTexteIncolumn2([A5:A7], ",", 3)
End Sub