Using Find method to find the closest value.

Hi all,
This should hopefully be an easy one to solve but not for me.

I have a code which searches for a number in another workbook using the find method.

There are 2 problems:

1) When the user enters a value that doesn't exist in the workbook, i need it to find the next closest value, which it currently does not do.

2) The number values in the workbook it is searching, are formatted as strings and not numbers, I think this will need to be considered also.

Currently this code works perfect if the value exists, but if it doesn't exist it then throws an error.

Here is the code:

Please Login or Register  to view this content.

I've highlighted the line which needs to be looked at, as sFind2 is the variable which needs to be found, and if not matched it needs to find the closest value.

I hope someone can help.

Yappa

Maybe it isn't so easy then.

I've also tried adjusting this line to:

Please Login or Register  to view this content.

and have also tried:

Please Login or Register  to view this content.

Using these lines it wont even find the value entered if it exists in the other workbook, where it usually does with the original line.

Currently, columnA of the designated workbook consists of numbers 1000, 1100, 1200, 1300.... etc. If the user enters 1200, the variable sFind2 takes on that value and the code searches for it. If however the user enters 1156, I need it to find the closest value, which in this case would be 1200.

Is there a way to do this?

Yappa

Hi mudraker,

Here is the workbook it is looking at, it will have more numbers in there later on, but for the time being its only for getting the code to work.

Yappa