Find lower and greater number

[Awuu]

Hello,
I'm new in VBA and I'm looking for help with my problem. I have numbers in A1 to A20 in order from 1 to 20. For number 4.5 I want to get 2 outputs:
-first greater number then 4.5 =>5
-first lower number then 4.5 = >4.


For greater number I found solution but for lower number I don't know to how start looking for first number which is lower then 4.5. In my case is output for lower number 1.


I will be grateful for any help. :)



Sub pr()


a = 4.5
For Each b In Range("A1:A20").Cells
If b.Value > a Then
GreNum = b.Value
Exit For
End If
Next


MsgBox "Greater number is " & GreNum


For Each c In Range("A1:A20").Cells
If c.Value < a Then
LowNum = c.Value
Exit For
End If
Next


MsgBox "Lower number is " & LowNum




End Sub

[Paul_Hossler]

You just need to check the NEXT cell to see if it's > than the one you're checking. Then the one you're checking is just below

I suggest you use Option Explicit which requires all variables to be Dim-ed and which helps avoid silly errors, and I find it helpful to use meaningful (or self documenting) variable names




Option Explicit

Sub pr()
Dim Cutoff As Double
Dim C As Range
Dim GreNum As Long, LowNum As Long

Cutoff = 4.5

For Each C In Range("A1:A20").Cells
If C.Value > Cutoff Then
GreNum = C.Value
Exit For
End If
Next
MsgBox "Greater number is " & GreNum


For Each C In Range("A1:A20").Cells
'Look at the NEXT cell to see if it's > the CutOff
' C.Offset(1,0) is 1 row more that C's row, but 0 column adjustment
If C.Offset(1, 0).Value > Cutoff Then
LowNum = C.Value
Exit For
End If
Next


MsgBox "Lower number is " & LowNum
End Sub

[Bob Phillips]

If the data is as you say, why not just use


Sub pr()
Dim a As Double
Dim GreNum As Double, LowNum As Double

a = 4.5
GreNum = Application.Ceiling(a, 1)
LowNum = Application.Floor(a, 1)

MsgBox a & " lies between " & LowNum & " and " & GreNum
End Sub

[Awuu]

Thank you!


I have another question if you might help. I added another array of numbers. This time from B1 to B20 with numbers from 0.1 to 2.


What i want to do is:
when i get greater number, let's say that is 5 from A5, i also want to get a number which is in B5. I also want to do this for lower number (for A4 give number from B4).