vba coding errors

Hi,

i have a spreadsheet which is created using macros to filter and remove unnecesary data. This is uploaded for others to use but i need to use this base document to issue copy out some specific data but the macros used initially do not work anymore.

Original macro example

ActiveSheet.Range("$A$5:$AF$1500").AutoFilter Field:=31, Criteria1:= _
"<>*dispute*", Operator:=xlAnd

I tried using both of the following but get a runtime error 1004.

ActiveSheet.Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

I also tried the following but it generates error 448

ActiveSheets.AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

Sheets("Department").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

Any idea why this is now happening and how to fix it?

Thanks

Mark

excel

Macros and VBA

JKPieterse
Sep 17, 2018
The code fails because the range addresses are wrong. Remove all dollar signs at the end of the addresses, example:

change:

Sheets("Department").Range("$A$1:$AJ$1500$").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

to:

Sheets("Department").Range("$A$1:$AJ$1500").AutoFilter Field:=1, Criteria1:= _
"<>*DVA*", Operator:=xlAnd

5 Replies

Karl STEWING
Copper Contributor
Sep 17, 2018
Hello Mark -

For the first ones, it seems that is a typo
ActiveSheet.Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= "<>*DVA*", Operator:=xlAnd
Should rather be $AJ$1500 ?

There also a typo in this one: ActiveSheets.AutoFilter Field:=1, Criteria1:= "<>*DVA*", Operator:=xlAnd
The extra s shouldn't be there.

And for the last one, you'll need a Range, I don't think you can Apply the AutoFilter command to an entire spreadsheet.

Hope this helps.
- Karl
JKPieterse
Silver Contributor
Sep 17, 2018
What error(s) do you get?
- Mark Lee
  Copper Contributor
  Sep 17, 2018
  Sheets("Department").AutoFilter Field:=1, Criteria1:= _
  "<>*DVA*", Operator:=xlAnd
  
  Run-time error '448': Named argument not found
  
  Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
  "<>*DVA*", Operator:=xlAnd
  Run-time error '1004': Application-defined or object-defined error
  
  full macro at this point is
  Sub mcrOpen()
  '
  ' mcrOpen Macro
  ' open file
  '
  Range("A5").Select
  Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=1, Criteria1:= _
  "<>*DVA*", Operator:=xlAnd
  Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=35, Criteria1:= _
  "<>*dispute*", Operator:=xlAnd
  Sheets("Department").Range("$A$5:$AJ1500$").AutoFilter Field:=11, Criteria1:="-3"
  Sheets("Department").Range("$A$5:$AJ1500$").SpecialCells _
  (xlCellTypeVisible).EntireRow.Copy
  
  Workbooks.Open Filename:="C:\Users\1402324\Desktop\Payment due within 3 days.xlsx"
  Range("A3").Select
  ActiveSheet.Paste
  
  It fails at first filter choice. my ultimate aim is to filter data, paste to a different workbook, open and draft email. i will do a little manual edit and add file to email, send and then run a macro for a different filter value
  - JKPieterse
    Silver Contributor
    Sep 17, 2018
    The code fails because the range addresses are wrong. Remove all dollar signs at the end of the addresses, example:
    
    change:
    
    Sheets("Department").Range("$A$1:$AJ$1500$").AutoFilter Field:=1, Criteria1:= _
    "<>*DVA*", Operator:=xlAnd
    
    to:
    
    Sheets("Department").Range("$A$1:$AJ$1500").AutoFilter Field:=1, Criteria1:= _
    "<>*DVA*", Operator:=xlAnd

Forum Discussion

vba coding errors

5 Replies

Resources