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

    Problem Setting Range (Excel 2000)

    I have a procedure that needs to establish the first and last rows of a given value in a sorted list. The present code is:-

    Set rngStart = Worksheets("Cust_Det").Range("A1:A1750").Find(What :=GetSalesArea$, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
    StartRowNo = rngStart.Row
    StartRow$ = Format(StartRowNo, 0)
    NextArea$ = Format(Worksheets("Detail").Range("NextSalesArea") .Value, 0)
    Set rngEnd = Worksheets("Cust_Det").Range("A1:A1750").Find(What :=NextArea$, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
    SearchFormat:=False)
    EndRowNo = rngEnd.Row - 1
    EndRow$ = Format(EndRowNo, 0)

    This works fine in Excel 2003, but crashes in Excel 2000.

    Can anyone suggest an alternative way of doing this that will work in Excel 2000?

    Thanks,

    Mike Jones

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Setting Range (Excel 2000)

    What is the error message you get? Can you single-step through the code in Excel 2000 or won't it start at all? If you can single-step, on which line does the error occur?

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

    Re: Problem Setting Range (Excel 2000)

    Sorry, Hans, I should have said.

    It fails on the first line of this section of the code (Set rngstart) with run-time error 448 - Named argument not found.

    Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Setting Range (Excel 2000)

    Searching for formatting was introduced in Excel 2002. Try omitting the

    <code>, SearchFormat:=False</code>

    from both Find instructions.

    By the way, you can also find the last occurrence of GetSalesArea by searching for GetSalesArea itself, with <code>SearchDirection:=xlPrevious</code> instead of <code>SearchDirection:=xlNext</code>. You don't need to subtract 1 from the row number then. And formally, you should have <code>SearchOrder:=xlByColumns</code> in both Find instructions, but in this case, it doesn't matter, since the search range has only one column.

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

    Re: Problem Setting Range (Excel 2000)

    Thanks, Hans. That did the trick.

    Thanks also for the tip about xlPrevious, that will be very useful.

    By the way, as soon as I fixed the Find problem, I ran into another one with a Sort, but I was able to track down the new parameter and remove it. Do you know if there's a resource somehwere that lists the available parameters in the different versions?

    Mike

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Setting Range (Excel 2000)

    > Do you know if there's a resource somehwere that lists the available parameters in the different versions?

    Sorry, I don't. Hopefully someone else will have a suggestion.

Posting Permissions

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