Thread: Max Value in a string (XL 97-->)

1. 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 Function

2. 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

3. 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)

4. 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?

5. Re: Max Value in a string (XL 97-->)

Now I understand, you have surpassed yourself, thank you

6. 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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•