Results 1 to 6 of 6
Thread: Max Value in a string (XL 97>)

20050126, 10:01 #1
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Max Value in a string (XL 97>)
I feel an explanantion is required prior to asking the question!
I am researching a numerical phenomena ( I don't want to say too much about it at the moment as it may appear on the Puzzle board soon!). What is required is a solution that will be able to look at a specific number and rearrange its digits so that the highest value is found i.e. if the number is 231 the function changes it to 321. Further to this I would like to find the minimum number from its combination ie 123.
I was playing around with this last night and wrote a function (Don't be too hard on me as it was late <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15> ). This function works and did the job I asked of it but to my eye looks a bit fixed and cumbersome as I would like to do the numerical proof for 4,5 and 6 figure numbers, As you will see my code only does 2 and 3 figures.
Can anyone think of a more streamlined approach to my problem or suggest any tweaks to my code that would facilitate this.
Function KapMax(Kap)
Dim intA, intB, intC, intD, intE, intF
Dim intKapA, intKapB, intKapC
Dim intKapMax
If Len(Kap) < 3 Then
intKapA = Mid(Kap, 1, 1)
intKapB = Mid(Kap, 2, 1)
intA = intKapA & intKapB
intB = intKapB & intKapA
intKapMax = Application.WorksheetFunction.Max(intA, intB)
Else
intKapA = Mid(Kap, 1, 1)
intKapB = Mid(Kap, 2, 1)
intKapC = Mid(Kap, 3, 1)
intA = intKapA & intKapB & intKapC
intB = intKapA & intKapC & intKapB
intC = intKapB & intKapA & intKapC
intD = intKapB & intKapC & intKapA
intE = intKapC & intKapA & intKapB
intF = intKapC & intKapB & intKapA
intKapMax = Application.WorksheetFunction.Max(intA, intB, intC, intD, intE, intF)
End If
KapMax = intKapMax
End FunctionJerry

20050126, 12:06 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Max Value in a string (XL 97>)
Do you only want the maximum or minimum digit, or do you want to sort the digits ascending/descending? If you want the min and max, you can use these functions:
Function MaxValue(varInput As Variant) As Variant
Dim i As Integer
If Len(varInput) = 0 Then
MaxValue = ""
Else
MaxValue = Left(varInput, 1)
For i = 2 To Len(varInput)
If Mid(varInput, i, 1) > MaxValue Then
MaxValue = Mid(varInput, i, 1)
End If
Next i
End If
End Function
Function MinValue(varInput As Variant) As Variant
Dim i As Integer
If Len(varInput) = 0 Then
MinValue = ""
Else
MinValue = Left(varInput, 1)
For i = 2 To Len(varInput)
If Mid(varInput, i, 1) < MinValue Then
MinValue = Mid(varInput, i, 1)
End If
Next i
End If
End Function

20050126, 12:08 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Max Value in a string (XL 97>)
And if you want to sort, use this:
Function SortValue(varInput As Variant, Optional varDescending As Variant = False) As Variant
Dim i As Integer
Dim varArray() As Variant
If Len(varInput) = 0 Then
SortValue = ""
Else
ReDim varArray(1 To Len(varInput))
For i = 1 To Len(varInput)
varArray(i) = Mid(varInput, i, 1)
Next i
SortArray varArray, varDescending
For i = 1 To Len(varInput)
SortValue = SortValue & varArray(i)
Next i
End If
End Function
Sub SortArray(varArray As Variant, Optional varDescending As Variant = False)
Dim i As Integer
Dim j As Integer
Dim varTemp As Variant
For i = LBound(varArray) To UBound(varArray)  1
For j = i + 1 To UBound(varArray)
If varDescending = True Then
If varArray(i) < varArray(j) Then
varTemp = varArray(i)
varArray(i) = varArray(j)
varArray(j) = varTemp
End If
Else
If varArray(i) > varArray(j) Then
varTemp = varArray(i)
varArray(i) = varArray(j)
varArray(j) = varTemp
End If
End If
Next j
Next i
End Sub
Use like this:
=SortValue(A1)
or
=SortValue(A1,TRUE)

20050126, 12:32 #4
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Max Value in a string (XL 97>)
Excellent Hans
This provides the minimum value of the number: 98702341 => 01234789. I can do the rest to get rid of the leading 0.
I assume that if I want to get the max. number I just change varDescending in line 8 of SortValue to varAscending?Jerry

20050126, 12:43 #5
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Max Value in a string (XL 97>)
Now I understand, you have surpassed yourself, thank you
Jerry

20050126, 12:45 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Max Value in a string (XL 97>)
No, varDescending is an argument to the SortValue function. If you supply FALSE or omit it, the digits are sorted ascending; if you supply TRUE, they are sorted descending.
SortValue(98702341) and SortValue(98702341,FALSE) result in 01234789.
SortValue(98702341,TRUE) results in 98743210.