Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping #REF in code (XL97 SR2 A2K SR!)

    I am writing a procedure to read through a series of Excel 97 spreadsheets and convert the contents into an Access 2000 database.
    However, several of the cells in the sheets have errors in their formulas, e.g.

    =(#REF!-C7)/0.0125

    What I want to do is report the error and save the details into an Access table. The problem is that however I try to reference
    the cell I keep getting Error 2023 come up on the first line of the following code :


    If Mid(objXLSheet.Cells(intRow, intCol).Formula, 3, 4) = "#REF" Then
    intPos = 1
    Else
    intPos = InStr(objXLSheet.Cells(intRow, intCol), strQuote)
    End If

    Any ideas on how I can stop this happening ?

    Thanks for your help

    Nick

  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: Trapping #REF in code (XL97 SR2 A2K SR!)

    Nick

    Lets get the obvious out of the way:
    1) objXLSheet does refer to a worksheet?
    2) intRow and intCol do have values? I would suggest that you change intCol to a LONG datatype, because in Excel 97 and > you have 65536 rows, more than an INTEGER datatype can handle.
    3) Did you try and see if the FIND function can do the trick for you? I think it is better since it will scan the whole string for the part that you want to find, not just the 3rd through 6th characters.

    OK lets me know.

    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
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Trapping #REF in code (XL97 SR2 A2K SR!)

    Use VBA's IsError:

    If IsError(Mid(objXLSheet.Cells(intRow, intCol).Formula, 3, 4)) Then
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping #REF in code (XL97 SR2 A2K SR!)

    Nick, is <pre>If TypeName(objXLSheet.Cells(intRow, intCol).Value) = "Error" Then</pre>

    good enough. This will also trap other errors, even #N/A, but hopefully, it will meet your needs. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping #REF in code (XL97 SR2 A2K SR!)

    Thanks for the replies.

    TypeName certainly did the trick.

    BTW, ISError returned True against the contents of the cell, but False for the cell formula. One to be aware of.

    Also BTW, IsError does not appear in the Visual Basic Help index. It goes from IsEmpty to IsExpanded. I wonder why that is ?

    Thanks to everyone again

    Nick

Posting Permissions

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