Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    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 Function
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)

  4. #4
    Platinum Lounger
    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

  5. #5
    Platinum Lounger
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

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