Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finicky Find (XL2000)

    Thought I would share a problem that I found using the Find function. I was using it to find a value in a named range. The values are always there. It worked good in the worksheet and then all of a sudden it wouldn't find the value if it was larger than 9. I would get an error 91 and crash. I spent hours trying to find a solution. Then it dawned on me that I had changed the column width of the named range and .Find didn't look at the actual value but the value that is displayed i.e. ## !!

    Changed the code to use Application.Match instead.

    If anybody knows of another solution please advise.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Finicky Find (XL2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Thom

    You mean Excel was confiused by the ##, because the column width was not enough to display 2 digits? I don't this is the case, but we all know that Excel does some crazy things sometimes.

    Did you try and increase the width of the column, just as a test, and see if it works the old way?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finicky Find (XL2000)

    Yup! When I change the column width everything is OK, set the column width down so only single digits display and it willl not work.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finicky Find (XL2000)

    Are you searching for the formula or the value? The value it searches is the DISPLAYED value so if the column width is too small it will NOT be found since the DISPLAY ( the VALUE) is "###". The formula might give you want you want if there are numbers in the cell.

    It changes whether you find in the value or formula. It also changes HOW you have the format of the cell!

    The actual "value" of the cell will change if you display as a date and the serialdate is no longer able to be "FOUND": neither as a value or formula, but he longdate (and this might be based on regional settiings) and the display date are the items searched, NOT the actual number!

    Steve

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finicky Find (XL2000)

    Steve,

    I tried using xlFormulas vs xlValues in my project but got the same resulting error. I am finding integers that are always present. The cell format can change from number to general.

    I set up a test worksheet and the xlFormulas will find the number. I even changed cell formats and it still finds the number in the test sheet. Still fails on my project. Could be some format problem but I'm not going to drive myself nuts anymore with it. Application.Match works and I will use that for now.

    Thanks

Posting Permissions

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