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
Bookmarks