Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Adelaide, Australia

    Unanswered: problems using sheets created in 97, in 2003

    Have upgraded office versions and am now using a number of spreadsheets that were created in Microsoft Excel 97 in Microsoft Excel 2003. All of them have fairly complex macros.

    Returning some intermittent errors.

    Have Googled for some answers - but as much as there are references to the specific error codes, -nothing specific about why they would be occurring now that they are being run in 2003.
    The spreadsheets + macros work fine in 97.

    Here is a chunk of the code, and the associated errors.

    Sub AccountClearFilterArea()
    End Sub

    Run-time error ‘-2147417848 (80010108)’: Method ‘ClearContents’ of object Range’ failed

    Sub SoldLogLastWeeksSales()
    Dim tmp1, tmp2 As String
    tmp1 = Range("SoldLogLastWeeksDate").Text
    tmp1 = ">" + tmp1
    Selection.AutoFilter Field:=1, Criteria1:=tmp1, Operator:=xlAnd
    End Sub

    Automation error (Error 440)

  2. #2
    Join Date
    Feb 2004
    Hi VickersTS, The 2 code samples you gave are pretty straight forward. I just tried them in Excel 2003 with a new workbook and didn't have much of a problem. Could still be a 97/2003 compatibility problem.

    In the first Example You can simplify this some like this.
    Sub AccountClearFilterArea()
    End Sub

    Now you would get an error on 'clearcontents' if the worksheet cells were locked and the worksheet protected. Make sure the worksheet is not protected.

    Sub SoldLogLastWeeksSales()
    Dim tmp1, tmp2 As String
    tmp1 = Range("SoldLogLastWeeksDate").Text
    tmp1 = ">" + tmp1
    Selection.AutoFilter Field:=1, Criteria1:=tmp1, Operator:=xlAnd
    End Sub

    With this you need to make sure the named range 'SoldLogWeeksDate' carried over to the 2003 version. The 'tmp1' variable is checking a date, you can use 'Range("SoldLogLastWeeksDate").Value' vs .Text but that shouldn't cause an error. the tmp2 var isn't being used in this snippet, you can remove the reference for it or change your code to use it in the Autofilter parameters.

    Another thing if you don't see the cause of the error try doing a web search on the error codes. You can usually get hits on the error codes that will give you some information.



  3. #3
    Join Date
    Mar 2005

    Thumbs up Re Excel problem

    Hi Bill and Paul

    I work with Paul and yes we are having some problems with Excel 2003. I will try some of your solutions Bill.

    There are so many areas where problems could be generated!. We were hoping to narrow down the search by going to this forum.

    Thanks Bill



  4. #4
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Hi Greg,
    it looks like you have an autofilter on when you are running
    ive extended your code for the autofilter to handle this and an attept to handle the case if the name is missing

    you can try it and see if it fits your needs
    'sub name changed as it was causing an object conflict
    Sub AddAutofilter()
        Dim tmp1            As String
        Dim bCheckName      As Boolean
        Dim wks             As Worksheet
        Dim rng             As Range
        Dim rngForName      As Range
        'set up object references
        Set wks = ActiveSheet
        Set rng = Selection
        'adjust rng to be the top left cell of selection
        'delete if inappropriate
        If Not rng.Column = 1 Then
            If Not IsEmpty(rng.Offset(0, -1)) Then Set rng = rng.End(xlToLeft)
        End If
        If Not rng.Row = 1 Then
            If Not IsEmpty(rng.Offset(-1, 0)) Then Set rng = rng.End(xlUp)
        End If
        'switch autofilter off if its on
        If wks.AutoFilterMode Then wks.AutoFilterMode = Not wks.AutoFilterMode
        'Check through the names in the worksheet if it doesnt exist try to add it
        For Each nme In Application.Names
            If nme = "SoldLogLastWeeksDate" Then
                bCheckName = True
                Exit For
            End If
        Next nme
        'trying to add the autofilter
        If Not bCheckName Then
            'find the correct location, adjust to your needs
            Set rngForName = wks.Cells.Find("Last weeks Date").Offset(0, 1)
            'if it isnt found infor user and drop out of sub
            If rngForName Is Nothing Then
                MsgBox "Please Define the Name SoldLogLastWeeksDate and rerun"
                Exit Sub
            End If
            'define the name in the new location
            Application.Names.Add "SoldLogLastWeeksDate", _
                        "=" & wks.Name & "!" & rngForName.Address
        End If
        'define criteria using doubles to prevent filter error
        'without it it shows nothing
        tmp1 = CDbl(Range("SoldLogLastWeeksDate").Value)
        tmp1 = ">" & tmp1
        'set the criteria of the autofilter, adjust number formats to prevent error
        'could use entirecolumn here but have set a range
        'as i dont know what else is in the column
        Range(rng, rng.End(xlDown)).NumberFormat = "0"
        rng.Autofilter Field:=1, Criteria1:=tmp1
        'reset numberformat
        Range(rng, rng.End(xlDown)).NumberFormat = "dd/mm/yyyy"
        'set object variables to nothing
        Set rng = Nothing
        Set rngForName = Nothing
    End Sub

  5. #5
    Join Date
    Mar 2005

    Thumbs up

    Hi Dave and Bill

    I have tried what Bill suggested about checking the named range transfer into 2003. The name range above was fine however there were some invalid ranges on the list. I have now deleted these which has certainly helped but has not solved the problem completely.

    I have found a code cleaner at
    which appears that it might have done the job but I'm not getting confident just yet.

    Dave thanks for the code I will try that as well. Autofilter worked fine in 97 with the current code but as you suggest it could be causing problems in 2003.

    Thanks for you help guys I will try out what you have suggested.



Posting Permissions

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