Searching for value using InputBox

[Steve41553]

I’m trying to search for a specific value in a list.This code looks for zeroes and it works

Selection.Find(What:="0.00 ", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

This code asks for a specific value and generates error 91:

f_val# = InputBox(prompt:="Enter value to search for.")
'    Application.FindFormat.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
'    Cells.Find(What:=f_val#, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate

I’ve tried removing the find number format line and entering a specific number into the code instead of the variable f_val#. Grateful for suggestions.

 

[anglais428]

You could create a variable (remember to declare the variable correctly) then have something like:
InputData = InputBox("Enter value to search for")

Then use the variable (InputData) in your existing code (i.e. replace the What:="0.00 " with What:= InputData

 

[RoryA]

Using Find with formatted cells, especially ones that pad the cell contents, is extremely troublesome when you specify to look in values, rather than formulas, since it looks for the displayed value, which in this case will have leading and trailing spaces (and the number of those will vary based on column width for the same number!) If you can, look in formulas instead of values, or use a partial cell match rather than a whole cell match. If you can't do any of that, I'd suggest using something like Match that isn't dependent on the display.

 

[Steve41553]

I'm posting this reply from home. Maybe members can read this one. I blame the systems at work.



Thanks for the replies. RoryA's suggestion gave me what I needed. I removed the formatting, searched and then reinstated it.

Columns("S:S").Select    


Selection.NumberFormat = "General"

'

f_val# = InputBox(prompt:="Enter value to search for.")

'

Cells.Find(What:=f_val#, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   

Columns("S:S").NumberFormat = "#,##0.00_);[Red](#,##0.00)"

I shall need to select column S and then format it (two lines of code) rather than saying Columns("S:S").NumberFormat = "General" so that I'm not searching in wrongly formatted parts of the sheet.

 

[RoryA]

I'm posting this reply from home. Maybe members can read this one. I blame the systems at work.

If you use IE at work, try setting it to use compatibility mode for this site.