|I'm not eactly sure what you are planning to do with the closest matching numbers once they are found, so I'm just going to toss out a few options to find those numbers. Once you try these, either modify them to meet your purpose or come on back with further questions.|
If your Column A looks like this,then try the 2 formulas I've posted below:
Put this in B1:
Put this in B2 and drag it down to the bottom of your list:
If you put e.g. 3 in C1, a 2 will be returned in B1, and a 4 will be returned in B2, showing you the 2 closest numbers.
Another option is this code, which will ask you for a number and then highlight the 2 closest numbers in Column A.
'Determine length of data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Clear highlighting in Column A
Range("A1:A" & lastRw).Interior.ColorIndex = xlNone
'Get value from user
myVal = Application.InputBox("Enter number", Type:=1)
'Quit if canceled
If myVal = False Then Exit Sub
'Loop through data, highlight cells with value above
'input and value below input
For x = 1 To lastRw
If Cells(x, 1) < myVal And Cells(x + 1, 1) > myVal Then
Cells(x, 1).Interior.ColorIndex = 6
Cells(x + 1, 1).Interior.ColorIndex = 6
For an input value of e.g. 5, the 4 and 6 will be highlighted.
Obviously, these suggestions are not the final answer to your question, but I hope they are a way to get you started.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.