VBA: Finding the largest value in array

Ezzer · Jun 20th 2003

Hi All,

I am populating an array from a named range and I need to be able to return the Lowest and Highest values from the array. The number of elements may differ and the values may not always be in numerical order. Could someone please help me out with an example code or link to an example.

Regards

Ezzer...:spin:

Richie(UK) · Jun 20th 2003

Hi Ezzer,

Is this what you had in mind:

Code

Sub Test()
    Dim varArray As Variant
    varArray = Range("NamedRange")
    MsgBox WorksheetFunction.Max(varArray)
End Sub

Change Max to Min for the lowest.

HTH

Ezzer · Jun 20th 2003

Richie,

Thanks for that, I imagined it would be something easy...

Ezzer...

Ezzer · Jun 20th 2003

I have to apologise for not giving enough info...

Here is a sample of the data that fills the array that I am trying to get the max value from:

A1780
A1790
A1800
A1810
A1850
A1900

I need to pass the value "A1900" from this example, is it possible?

Regards

Ezzer...:spin:

Dave Hawley · Jun 20th 2003

Hi Ezzer

If you can sort the data you have shown, it would simple be:

Code

Sub MaxValue()
MsgBox "Max is " & Range("A65536").End(xlUp)
End Sub

If not sorted and are preceded with others letters, use

Code

Sub IfNotSorted()
Dim i As Byte
Dim StrLetter As String
Columns(2) = Columns(1).Value


On Error Resume Next
    For i = 1 To 26
        StrLetter = Choose(i, "a", "b", "c", "d", "e", "f", _
                    "g", "h", "i", "j", "k", "l", "m", "n", _
                    "o", "p", "q", "r", "s", "t", "u", "v", _
                    "w", "x", "y", "z")
                    
        Columns(2).Replace What:=StrLetter, Replacement:="", _
                   LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Next i
On Error GoTo 0
MsgBox "Max is " & WorksheetFunction.Max(Columns(2))
End Sub

Display More

VBA: Finding the largest value in array

Participate now!

Share