Get next largest value in an array

User avatar
geedeearr
StarLounger
Posts: 52
Joined: 04 Feb 2010, 17:14
Location: Brookings, South Dakota

Get next largest value in an array

Post by geedeearr »

Hi All,
Given an array, say (2,3,6,8) and I have a value, which is a member of the array, what is the procedure to find the next largest value (member) in the array? Also, if the value I am using is the largest value, the "next largest value" would be the smallest value in the array.
I know that if I have the maximum value (8 in the array above), I can check if it is the largest value (upper bound) and if so its' "next largest value" would be 2 (lower bound),

Thank you.
gary

Those who dance are considered insane by those who can't hear the music. - George Carlin                    Image

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get next largest value in an array

Post by HansV »

In your example, I'd say that if the value you have is 8, the next largest would be 6. What exactly do you mean by "next largest"?
Best wishes,
Hans

User avatar
geedeearr
StarLounger
Posts: 52
Joined: 04 Feb 2010, 17:14
Location: Brookings, South Dakota

Re: Get next largest value in an array

Post by geedeearr »

Hi Hans,
Sorry, I mean the value which is the next largest in the array. If 2, then 3; if 3, then 6; if 6, then 8; if 8, then 2.

btw, this isn't too bad of an interface using my work phone...a Motorola Q9c. Now off to the dentist..

Thank you.
gary

Those who dance are considered insane by those who can't hear the music. - George Carlin                    Image

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get next largest value in an array

Post by HansV »

Assuming that your values will be of type Long, here is a function you can use:

Code: Select all

Function GetNextLargest(arrValues As Variant, lngValue As Long) As Long
  Dim i As Long
  Dim lngNextLarger As Long
  Dim lngMin As Long
  lngNextLarger = 2147483647
  lngMin = 2147483647
  For i = LBound(arrValues) To UBound(arrValues)
    If arrValues(i) < lngMin Then
      lngMin = arrValues(i)
    End If
    If arrValues(i) > lngValue And arrValues(i) < lngNextLarger Then
      lngNextLarger = arrValues(i)
    End If
  Next i
  If lngNextLarger = 2147483647 Then
    GetNextLargest = lngMin
  Else
    GetNextLargest = lngNextLarger
  End If
End Function
The code loops once through the elements of the array arrValues to determine (a) the smallest element larger than lngValue, and (b) the overall minimum element. If (a) is found, that value is returned, otherwise (b) is returned.

Examples of use:

Code: Select all

Debug.Print GetNextLargest(Array(2, 3, 6, 8), 3)
and

Code: Select all

Dim lngArr(1 To 4) As Long
Dim lngTestVal As Long
Dim lngRetVal As Long
lngArr(1) = 3
lngArr(2) = 2
lngArr(3) = 8
lngArr(4) = 6
lngTestVal = 8
lngRetVal = GetNextLargest(lngArr, lngTestVal)
Best wishes,
Hans

User avatar
geedeearr
StarLounger
Posts: 52
Joined: 04 Feb 2010, 17:14
Location: Brookings, South Dakota

Re: Get next largest value in an array

Post by geedeearr »

Thank you very much Hans. Perfect!
Sorry for the delay getting back. I was just now able to look at it.
gary

Those who dance are considered insane by those who can't hear the music. - George Carlin                    Image