[Brad Patterson]

Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and what position it¹s at
in this type of array * i.e. for the above, the maximum is 101, at position
3.

I¹ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can¹t get the position of that number.

I¹m attempting this with an array that is 1000 in size if that¹s going to
be a problem ...

Thanks,

Brad.)

[Alan Beban[_3_]]

Application.Match(Application.Max(Arr),Arr) or

Application.Match(Application.Large(Arr,1),Arr)

Alan Beban

Brad Patterson wrote:
Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and what position it?s
at in this type of array ? i.e. for the above, the maximum is 101, at
position 3.

I?ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can?t get the position of that number.

I?m attempting this with an array that is 1000 in size if that?s going
to be a problem ...

Thanks,

Brad.)

[Pete McCosh[_2_]]

Brad,
there's probably a simpler way, but you could loop through
the array testing all the values. This code works fine,
although you'll have to modify it if your highest value is
potentially below zero and if there are two or more equal
highest values it'll only identify the first, but I'm sure
you can cross that bridge when you come to it.

Sub BiggestInArray()

Dim TestArray(1000) As Integer, HighestValue As Integer,
HighestPos As Integer
HighestValue = 0

TestArray(1) = 3
TestArray(54) = 46
TestArray(897) = 24

For x = LBound(TestArray) To UBound(TestArray)

If TestArray(x) HighestValue Then
HighestValue = TestArray(x)
HighestPos = x
End If

Next x

MsgBox ("The highest value in the array is " &
HighestValue & " at position " & HighestPos & ".")

End Sub

Cheers, Pete

-----Original Message-----
Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and
what position it¹s at
I¹ve had success with
Application.WorksheetFunction.Large(Arr, 1)
But still can¹t get the position of that number.
I¹m attempting this with an array that is 1000 in size
Thanks,

Brad.)

[Patrick Molloy[_3_]]

WorksheetFunction.Match(max, arr, False)

Example
Sub test()

Dim ar(1 To 5) As Long
Dim i As Long
Dim max As Long
For i = 1 To 5
ar(i) = Int(Rnd * 1000)
Next
max = Application.WorksheetFunction.max(ar)
Debug.Print max; " @ "; _
WorksheetFunction.Match(max, ar, False)

End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and
what position it¹s at
in this type of array * i.e. for the above, the maximum
is 101, at position
3.

I¹ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can¹t get the position of that number.

I¹m attempting this with an array that is 1000 in size
if that¹s going to
be a problem ...

Thanks,

Brad.)

[Dana DeLouis[_5_]]

Just out of educational curiosity... On an array with 1,000 numbers, I
found that using Max was a little faster than Large by about 20-30% .

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Brad Patterson" wrote in message
...
Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and what position it¹s
at
in this type of array * i.e. for the above, the maximum is 101, at
position
3.

I¹ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can¹t get the position of that number.

I¹m attempting this with an array that is 1000 in size if that¹s going to
be a problem ...

Thanks,

Brad.)

[Brad Patterson]

This works very well. Just when I think there isn¹t a function to do what I
want ... There it is. ...

Thanks again,

Brad.)

in article , Patrick Molloy at
wrote on 10/7/03 10:00 PM:

WorksheetFunction.Match(max, arr, False)

Example
Sub test()

Dim ar(1 To 5) As Long
Dim i As Long
Dim max As Long
For i = 1 To 5
ar(i) = Int(Rnd * 1000)
Next
max = Application.WorksheetFunction.max(ar)
Debug.Print max; " @ "; _
WorksheetFunction.Match(max, ar, False)

End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Dim Arr(5) as integer

Arr(2) = 99
Arr(3) = 101

How do I then find out what the maximum value is, and
what position it¹s at
in this type of array * i.e. for the above, the maximum
is 101, at position
3.

I¹ve had success with

Application.WorksheetFunction.Large(Arr, 1)

But still can¹t get the position of that number.

I¹m attempting this with an array that is 1000 in size
if that¹s going to
be a problem ...

Thanks,

Brad.)