Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Unanswered: Customizing the "Find" Command

    I use the Find command quite often in my company's workbooks (example: finding a project number from our project list by typing in the project name). Now, 99% of the time I am looking for something in a value, not a formula (I think this is probably true for most users). The "Find" Dialog Box always defaults to look in formulas, and I always have to manually change the dialog to look in values.

    Does anyone know how to change the defaults for the dialog box to look in values? Or is there a way to get around this in a macro?

    Thanks,

    Aaron

  2. #2
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    hi Aaron,
    sorry but I'm in a hurry, you can try with this:

    - set up a macro with a shortcut association
    [code]
    WhatImlookingFor = InputBox(Prompt:="What are you looking for?", Title:="Find Values")
    Dim X As Range
    Set X = Cells.Find(what:=WhatImlookingFor, After:=ActiveCell, LookIn:=xlValues, _ lookat:= xlWhole)
    MsgBox X.Address
    [/cde]
    This will provide the first cell which matches your search condition...of course you can refine it a lot...Enjoy.
    FatherXmas

  3. #3
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    ops,
    I forgot to say you need to run the macro using the previously defined shortcut...

  4. #4
    Join Date
    Feb 2004
    Posts
    13

    Hung up in the debugger

    FatherXmas,

    Thanks for the code. This looks like it will the perfect solution! However, I tried it, and it got hung up in the debugger. This is my macro:

    Sub Find_value()
    '
    ' Find_value Macro
    ' Macro recorded 7/2/2004 by Aaron Coppersmith
    '

    '
    WhatImlookingFor = InputBox(Prompt:="What are you looking for?", Title:="Find Values")
    Dim X As Range
    Set X = Cells.Find(what:=WhatImlookingFor, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)
    MsgBox X.Address
    [/cde]
    End Sub

    The message that I got was:

    Run-time error '91'
    Object variable or With block variable not set.

    Do you know how to get past this?

    Thanks!

    Aaron

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Your probably getting this when X is Nothing

    try putting an if statement in something like

    If Not X Is Nothing Then
    msgbox X.Address()
    else
    msgbox "address not found"
    end if

    HTH

    David

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    There is an XL add-in that is perfect, and you can choose whether to look in cells with formulas or with constants. And it allows you to work with objects as well.

    It is called FlexFind, by Jan Pieterse (hosted on Stephen Bullen's site)

    http://www.bmsltd.co.uk/

    (although his site is temporarily down).

    Or try this site:

    http://www.jkp-ads.com/Download.htm#Flexfind
    Last edited by shades; 07-02-04 at 13:11.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •