Thread: Better "Find" Function for numbers on a worksheet.

I have a very large worksheet with purchases annotated and when I try to reconcile with a Account system (manually) it is hard to manually search for a number when trying to match. The problem with the "find" function is it will only digest whole number, if I enter 41.23 it tells me it doesn't exist yet I know it does so it forces me to scan manually for the number. Is there a macro I could run that would do a better job of searching or finding a number with decimal places?

Hi

The issue may be that you are looking for values, not formulae i.e. you can search for 41.23 when that value is typed in, however, if it is the result of a calculation, the default search won't find it. Excel's default search is to look in the cell formula, not the cell value. The same applies to rounding i.e. your 4.23 is actually 4.228. Values will find 4.23, even though it is rounded, Formula won't.

You need to go to press the 'Options' button in the Search dialogue box, and change 'Look In' to 'Values'.

To avoid doing this each time, you could set up a macro. I don't know how familiar you are with macros, and whether you want this function available in all spreadsheets or just a specific one. This would determine how you implement it. You could create an add-in, then have the function always available, or simply add it to the one spreadsheet and assign a hotkey to it, so that it is easy to access in that spreadsheet.

The macro would be:

Sub FindValue()

Dim strSearch As String

strSearch = InputBox("Text to find")
If strSearch = "" Then End

Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        
End Sub

Cheers, Rob.