Searching for nearest number

[masarup11]

Good day, i need help with this one.. i need a loop that will use the lower number inside an array if the

(searchNum - result) < 0 or negative

for example searchNumber = 9300 / result = 10000 but since it will return as negative result will use 7083

Dim numbers(), arrItem, maxItem, result, searchNum

        'search number
        searchNum = Cells(x, 3) - Cells(x, 4)
        numbers = Array(1, 4167, 4583, 5417, 7083, 10000, 14583, 25000)
        
        'get max of array item
        maxItem = Application.Max(numbers)


    
        'loop through each array item
        For Each arrItem In numbers


            If Abs(searchNum - arrItem) < maxItem Then
                maxItem = Abs(searchNum - arrItem)
                result = arrItem


                If (searchNum - result) < 0 Then
                    'use lower number in array
                End If


            End If       


        Next arrItem

 

[Rick Rothstein]

You can get your max number more directly this way. First, change the numbers variable from a dynamic array to a normal String variable or, given the way you declare your variables, a Variant, then use this code (here I hard coded the 9300, but you can use whatever method you want to set the searchNum variable's value...

[table="width: 500"]
[tr]
	[td]searchNum = 9300
numbers = "1,4167,4583,5417,7083,10000,14583,25000"
maxItem = Evaluate("MAX(IF(" & searchNum & ">{" & numbers & "},{" & numbers & "}))")[/td]
[/tr]
[/table]

 

[NMa91]

Try this:

Dim numbers(), arrItem, maxItem, result, searchNum
    
'search number
searchNum = 9300
numbers = Array(1, 4167, 4583, 5417, 7083, 10000, 14583, 25000)
'get max of array item
maxItem = Application.Max(numbers)

For i = 0 To UBound(numbers)
    If Abs(searchNum - numbers(i)) < maxItem Then
        maxItem = Abs(searchNum - numbers(i))
        result = numbers(i)
        If (searchNum - result) < 0 Then
        'use lower number in array
            a = numbers(i - 1)
        End If
    
    End If
Next i

 

[StephenCrump]

It's not totally clear what results you want to return.

But if your numbers are always sorted in ascending order, perhaps a simple:

result = Application.HLookup(searchnum, numbers, True)

also checking that searchnum is not less than the first value in the numbers array.

 

[masarup11]

It's not totally clear what results you want to return.

But if your numbers are always sorted in ascending order, perhaps a simple:

result = Application.HLookup(searchnum, numbers, True)

also checking that searchnum is not less than the first value in the numbers array.

that was my mistake sorry.. i was planning on returning the value of

result
Cells(x,7) = result 'for example

 

[StephenCrump]

It's not totally clear what results you want to return.

Thanks, but the question was more about how you wanted to calculate results, rather than where you wanted to put them.

It's clear, e.g. from Rick's and my approaches, that you don't need looping code to get a result.

But do you need VBA code at all? Rather than hardcoding numbers in a VBA sub, why not use an Excel table, allowing quick, dynamic update, and use a simple Excel formula like VLOOKUP to return the desired results to cell(s) of your choice?