CODEWITHSUNDEEP
vbaexcel
NewbieVB
NewbieVB

Reputation: 29

Excel VBA Nearest Value in Array

I have the following data in a array

Dim numbers ()
numbers = Array(1, 2083, 3050, 4030, 6000)

I want to get the closest value from my array when i put a number in Range("A1")

Upvotes: 1

Views: 5002

Answers (2)

Kresimir L.
Kresimir L.

Reputation: 2441

You can also try this VBA code.

Sub closestnumb()

Dim numbers()
numbers = Array(1, 2083, 3050, 4030, 6000)
t = WorksheetFunction.Max(numbers)
For Each r In numbers
        u = Abs(r - Range("a1").Value)
        If u < t Then
            t = u
            a = r
        End If
    Next
 MsgBox a
End Sub

Upvotes: 1

Mrig
Mrig

Reputation: 11702

Try this

Sub Demo()
    Dim numbers(), arrItem, maxItem, result, searchNum
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")  'change Sheet1 to your data sheet
    searchNum = ws.Range("A1")              'search number
    numbers = Array(1, 2083, 3050, 4030, 6000)
    maxItem = Application.Max(numbers)      'get max of array item

    For Each arrItem In numbers             'loop through each array item
        If Abs(searchNum - arrItem) < maxItem Then
            maxItem = Abs(searchNum - arrItem)
            result = arrItem
        End If
    Next arrItem
    MsgBox result       'display result
End Sub

Got this from here.

Upvotes: 3

Related Questions