Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Failed Find using Range (Excel 2003)

    I have some strange behaviour in a Find command using a range. The command is:-

    Set rng = Worksheets("Cust_Det").Range("A2:A500").Find(What: =GetSalesArea$, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    StartRowNo = rng.Row

    If the cell containing the data required is in the top half (roughly) of the range, the Find works OK, but if it is lower down, it produces an error. In debug, rng = Nothing and rng.Row shows (unsurprisingly) 'Object variable not set'.

    I can get round this by looping through the cells to find the target, but I'm just interested to know why this happens. Incidentally, if I sort the target rows in reverse, the behaviour is the same. The targets that were found before now cause an error (because they're in the lower half), and the ones that failed are found.

    Regards,

    Mike

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Failed Find using Range (Excel 2003)

    Hi Mike,
    Can you post a sample workbook that demonstrates the problem?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Feltham, Middlesex, England
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Failed Find using Range (Excel 2003)

    Hello Rory,

    The original workbook had a lot of financial information in it and I'm embarrassed to say that, in creating a dummy one to post, I discovered the problem.

    I'd mis-typed the cell range, resulting in it being shorter than intended, which explains why it only found cells in the top half!

    Sorry to have wasted your time with this one, but many thanks for responding.

    Regards,

    Mike

Posting Permissions

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