Results 1 to 3 of 3

Runtime error 1004 using arrays

Threaded View

  1. #1
    Registered User
    Join Date
    05-13-2011
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2003
    Posts
    1

    Post Runtime error 1004 using arrays

    Am a little new with VBA programming, and have made a short program to given a simpler interface to using the autofilter function in a worksheet. The program works fine in Excel 2007, however in Excel 2003 the program gives the runtime error 1004 with regard to range.autofilter. The code is as below

    The error while debugging is relating to the array that I have defined.

    
    Dim ShipArray(14) As Variant
    Dim MonthArray(4) As Variant
    Dim ArrayValue As Integer
    Dim ArrayCol As Integer
    Dim ArrayValue1 As Integer
    Dim ArrayCol1 As Integer
    
    'have deleted some lines here where data is entered into the worksheet depending on whether checkboxes are ticked. Column BA is used and rows 1 to 15 are used. If the checkbox is not ticked, its left blank, if ticked then a text is entered in the cell.
    
    
    'creating array of months
    ArrayValue1 = 0
    
    For ArrayCol1 = 1 To 4
        If Cells(ArrayCol1, 54).Value <> "" Then
            MonthArray(ArrayValue1) = Cells(ArrayCol1, 54).Value
            ArrayValue1 = ArrayValue1 + 1
        End If
    Next ArrayCol1
    
    'creating array of ships
    ArrayValue = 0
    
    For ArrayCol = 1 To 15
        If Cells(ArrayCol, 53).Value <> "" Then
            ShipArray(ArrayValue) = Cells(ArrayCol, 53).Value
            ArrayValue = ArrayValue + 1
        End If
    Next ArrayCol
    
    
        ActiveSheet.Range("$A$1:$G$996").AutoFilter Field:=1, Criteria1:=ShipArray, Operator:=xlFilterValues
        ActiveSheet.Range("$A$1:$G$996").AutoFilter Field:=2, Criteria1:=ComboYear.Value
        ActiveSheet.Range("$A$1:$G$996").AutoFilter Field:=3, Criteria1:=MonthArray, Operator:=xlFilterValues
        ActiveSheet.Range("$A$1:$G$996").AutoFilter Field:=7, Criteria1:=Range("BC1").Value
    
    Hope the info is sufficient.

    The following line gives the error
    ActiveSheet.Range("$A$1:$G$996").AutoFilter Field:=1, Criteria1:=ShipArray, Operator:=xlFilterValues
    with regard to Criteria1

    Thanks in advance
    Last edited by sonyrajan; 05-14-2011 at 09:23 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1