Picking Next Highest Value

[Robb0688]

Hello,

I have a sheet I'm trying to build that will allow me to compare values in bands/tiers. The tiers aren't always consistent. Sometimes they count by 5s up through 250 and then 10s from there on, or count by 10s at the start, etc. I want the function to take the values I may input and find the next highest number in my established pricing tiers without going over and return the price associated with it. I'm having trouble writing a function that doesn't go over and that also handles exact matches ok. If I input 20 and I have pricing for 20, I don't want it to round up to 25 or 30.

 

[Jamie McMillan]

Hello Rob,

If the 5, 10, 15, .... are in column A - and you input your number in cell B1 - the following code will insert the next number higher or equivalent into cell C1

Sub NextHighest()
Dim i As Integer
    For i = 1 To 24
    If Cells(i, 1).Value = Range("B1").Value Then
    Range("C1").Value = Cells(i + 1, 1).Value
    Exit Sub
    End If
    If Cells(i + 1, 1).Value > Range("B1").Value Then
    Range("C1").Value = Cells(i + 1, 1).Value
    Exit Sub
    End If
    Next i
End Sub

Jamie

 

[Robb0688]

Hello Rob,

If the 5, 10, 15, .... are in column A - and you input your number in cell B1 - the following code will insert the next number higher or equivalent into cell C1

Sub NextHighest()
Dim i As Integer
    For i = 1 To 24
    If Cells(i, 1).Value = Range("B1").Value Then
    Range("C1").Value = Cells(i + 1, 1).Value
    Exit Sub
    End If
    If Cells(i + 1, 1).Value > Range("B1").Value Then
    Range("C1").Value = Cells(i + 1, 1).Value
    Exit Sub
    End If
    Next i
End Sub

Jamie

Thank you, that worked!