Results 1 to 5 of 5
  1. #1
    Brian Roby
    Guest

    .Find not Working? (Excel2k SR1a VBA)

    When using the inbuilt "Find" routine from Excels VBA I am experiencing a problem where the find routine is not returning the correct range object.
    for example with the following code the stated range includes all data types ie text, numbers and blank cells. We are supplying a number via the SchedNo variable and want the range object of the cell that this number is in (if it exists) within the stated range.
    dim SchedNo_Loc as Range
    With Sheets(EventName).Range("BR269:BR740")
    Set SchedNo_Loc = .Find(SchedNo, LookIn:=xlValues)
    End With
    However... this routine appears to screw up almost every other time with no apparent cause for the failure or success...

    One solution that has worked for us is to loop thru all the cells using "For Each..." but this is alot slower!!!

    Any help for us on this one greatly appreciated.
    Thanks
    Brian.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .Find not Working? (Excel2k SR1a VBA)

    The first thing that I see is that it looks like the sheet name "EventName" needs to be in quotes in the With statement. Could that possibly be causing your problem (I would expect that to cause a Subscript out of range error though).

    If not, can you give us a little better definition of what you mean by "appears to screw up?" I copied and pasted your code into my Excel 97 and after fixing the sheet name problem, it seems to work fine for me.
    Legare Coleman

  3. #3
    Brian Roby
    Guest

    Re: .Find not Working? (Excel2k SR1a VBA)

    Legare,
    When refering to the routine screwing up I mean that I am not getting consistent results. ie sometimes the correct range object is returned and sometimes, even if the target exists, the correct range object is not returned, or no range object is returned at all (ie not found).
    As for the EventName qualifier I should have clarified that as being a string variable set from another section of code. sorry about that... otherwise just hard code to a sample page for this example.
    Hope this helps.
    Brian.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .Find not Working? (Excel2k SR1a VBA)

    There are a few things about .Find that I noticed when I was testing it before answering your question (I had never used it in VBA before) that might cause it to look like it is not working properly.

    1- .Find starts looking AFTER the first cell in the range. Therefore, if you tell it to find something that is in the first cell, but can also be found in a later cell, it will find it in the later cell. I found that I had to use the After parameter to tell it to start looking after the last cell in the range to find something in the first cell that was also in any other cell.

    2- I gave it a range of ten cells containing the numbers 1,2,3,4,5,6,7,8,9,10, and asked it to find 1 with no After parameter. If returned the last cell in the range which contained 10. So, it apparently started looking in the second cell in the range, and did a string search for any cell containing a "1" in the cell text.

    Could either of those explain what you are seeing?
    Legare Coleman

  5. #5
    Brian Roby
    Guest

    Re: .Find not Working? (Excel2k SR1a VBA)

    Legare,
    Hmm... This will give me some areas to try.
    I will post a reply after completing some test data.
    Thanks
    Brian.
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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