VBA - Static code to dynamic

[gan_xl]

Hi,

Please have a look at the attached file, "HB Data" is the original sheet and got the following code from this forum

Please Login or Register  to view this content.

The above code works fine if I have the data as per the 'HB Data' sheet, I know if additional data added as in the 'HB Data_new' then the above code won't work. So I need to convert the above code to dynamic.

Kindly help me please.

Originally I had

Please Login or Register  to view this content.

But it was not working, so I got help and above code from this forum.

Thanks

[Norie]

What, in words, is the code meant to do?

Also, why do you have the Select Case when the value of Position is fixed?

[Olly]

What are you actually trying to achieve?

[gan_xl]

Hi Olly,

My input value is '16', so its nearest smallest number is 14 (E2), its nearest smallest number position is 3 (E1) and its nearest biggest number is 19 (F2).
I got the nearest smallest number position value using match function and cross checking position value with the headers (1,2,3,4,5) if position value matches with the header value then the cell below matched header will be smallest number and the next cell will be nearest biggest number.

Please let me know if it is still not clear

Thanks

[Norie]

Where in the code are you inputting a value and determining the position?

[Olly]

Okay - here's a coupe of UDFs which should help:

Please Login or Register  to view this content.

[gan_xl]

Olly, It works fantastic. Thank you so much