Find function and column widths

I've been trying to get my VBA script to find a specific value in a column and return the corresponding row number. The code for this is reasonably straight forward but it has taken me most of yesterday to get it to work. The code is as follows:

Please Login or Register  to view this content.

After much head scratching, and by pure luck, I discovered that it wasn't working because the column containing the value that I was searching for was too narrow. The column contains the value I'm looking for and, if I select the cell manually, I can see the value in the Formula bar but for some bizarre reason, Excel can't find it. If I widen the column, hey presto, it finds it! I've since replicated this using the built-in Find function on the toolbar and it behaves exactly the same. Surely the Excel Find function doesn't work visually?!?!

Has anyone else experienced this and, if so, have any suggestions other than widening the column? I was actually intending to use the code to look up data in a hidden column, but looks like that's out of the question too.

Perhaps posting your sheet might help ? ( see yellow banner)

Please Login or Register  to view this content.

Alternative

Please Login or Register  to view this content.

Find only looks for displayed values...

Option 1 works for find - narrow column...lookin:=xlformulas

Or just autofit column widths prior to search...