Use VBA codes to Fnd the closst point and record the row

i have 10 points inserted in colume A, row 2-11(exclude row 1 due to title)
and 10 points(ie.random order 1-10) inserted in colume B, row 2-11
I have a point with value 5.8 in cell D4
and a point with value5.8 in cell D5
i wanna find the point in colume A which is closest to value in D4
and find the point in colume B which is cloest to value in D5

The answer should be the value 6,
but what i want to find is what row is the answer in, and show in the cell D6
so what should appear in D6 is 7, because the value 6 is in the 7th row.

can someone point me a direction of how to do this?
btw, i understand simple VBA code, im using direct cell reference here just to keep it simple.
To whoever cares about my question and spend time on it~
Thanks you! i am really grateful~~

It looks like are looking for two different values, the row in column A that contains the value that is closest to the value in cell D4, and the row in column B that contains the value that is closest to the value in cell D5.
Since you are looking for two different values, you probably want two cells to display those two values. You could use D6 for column A and D7 for column B, how does that sound?

If that is acceptable to you, use this code:

Please Login or Register  to view this content.

If there are any issues or questions, please ask.

Posting a workbook with an example and expected results would be a good thing.

It would be easier to help if you included all your requests in your original post. If you wanted it for an entire column instead of rows 2-11 and you don't know how to modify the code to do that yourself, plz include that as a request in your first post.

Its hard to understand what exactly you are looking for, but I've modified the code to go through all rows in columns 1 & 2, and also put the worst rows in cells D9 and D10. If you are doing this for multiple worksheets, use a simple for each loop. If you need help with that type for in the vb editor, put the cursor inside of the word and hit F1 to bring up the help.

Please Login or Register  to view this content.

Again, any more questions, please ask.

sry for the late reply,
the fact is that i may have misunderstood the question,


Thank you so much for helping~

is it possible that you leave me a email so i can send the specific sheet to you

Please don't. The point of a forum is to help all members learn. You can post your workbook here.

If I were you, I would remove my email address from your last post.

This posted example is significantly different from your first request. It deals with each row as a point, so you can't just find the closest and farthest number in each column. You have to use the distance formula, which is d=((x1-x2)^2 + (y1-y2)^2)^(1/2).

Here is the updated code:

Please Login or Register  to view this content.