Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel 2002 on win98 (winxp, office 2003)

    I wrote a macro for a client. I use office 2003, sp2 on winxp. Now i find out she uses office 2002 on windows 98. When she runs the utility it gets an uninterruped error, type 9. How do i find out what has changed between win98 and winxp?

    Here is some of my code...

    baseSearch:
    Set dRng = .Find(f, dRng, xlValues, xlWhole, xlByColumns, xlNext, True)

    If dRng Is Nothing And f = "Base" Then
    f = "Total"
    GoTo baseSearch
    End If

    If dRng Is Nothing Then
    If MsgBox("Searching for Base row. No cells in column A containing the text 'Base' or 'Total' alone were found. Is there another word or phrase to search for?", vbYesNo + vbQuestion, "Expected Text Not Found") = vbYes Then
    f = InputBox("Enter alternate search text", "Manual Intervention Required")
    If f <> "" Then
    GoTo baseSearch
    Else
    MsgBox "Cannot continue without locating base row. Please check or revise the file and try again.", vbCritical, "Cannot Process"
    Exit Sub
    End If
    End If
    End If

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

    Re: Excel 2002 on win98 (winxp, office 2003)

    If the first search (you haven't told us what the search string is, I'd guess "Base") fails, dRng is set to Nothing, and this will cause the second search (for "Total") to throw up an error, because in the instruction

    Set dRng = .Find(f, dRng, xlValues, xlWhole, xlByColumns, xlNext, True)

    the second argument is Nothing.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2002 on win98 (winxp, office 2003)

    sorry, this is entire code

    Very long piece of code (8400 characters!) moved to attachment by HansV

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

    Re: Excel 2002 on win98 (winxp, office 2003)

    My original comment still stands: if "Base" is not found, the second Find will result in an error. Apart from that, the code runs to completion in Excel 2002 on Windows XP, and I don't see anything that would cause it to fail on Windows 98.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2002 on win98 (winxp, office 2003)

    The delimited file that is being converted does have the word "base" in it. It runs fine on my pc with win xp and excel 2003 sp2. When it runs on her pc it gets that type 9 error. She has office 2002 on windows 98. I was wondering if there are differences in excel 2002 to excel 2003 like the colorindex property, the pagebreak property constants, for maybe the border type constants (xlEdgeLeft etc).

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Excel 2002 on win98 (winxp, office 2003)

    Hi jha,

    I doubt that it's anything to do with the colorindex property, the pagebreak property constants, or the border type constants, all of which have been around a lot longer than xl2002. A type 9 error is a 'subscript out of range', and your code appears to be susceptible to this on the line:
    Set dRng = .Find(f, dRng, xlValues, xlWhole, xlByColumns, xlNext, True)
    if 'Base' isn't found on the first pass. You can test that by commenting out your 'On Error GoTo ooops' line and seeing where the code fails. If the code is failing somewhere else, the same approach will help show you where.

    Could it be that your user doesn't have the correct input files in the correct folder?

    Also, following on from what Hans said, you might get better results if you reposition the line:
    Set dRng = dSht.Cells(1, 1)
    after the line:
    basesearch:

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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