Syntax Error

[Damian37]

Hello All,
After creating code to copy my filtered data within my raw data tab, I realized there is a value coming back that I would like to exclude. I'm currently filtering on "High", "Moderate-High". HOwever I'm also retrieving the value "Moderate" alone. I'm trying to exclude it with the following code, but I'm receiving a syntax error. I've tried moving the "Criteria2" portion to different areas, but nothing has worked so far.

Sub FilterMultipleCriteria()
'
 '   On Error Resume Next
    
' Filter by Multiple Criteria

 Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("Sheet1").Delete
  On Error GoTo 0
  Sheets.Add After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "Sheet1"


    Dim ws As Worksheet
    Dim FilterRange As Range
    Dim criteriaArray As Variant
    Dim CopyRange As Range
    Dim DestRange As Range
    Dim Cell As Range
    
    Set ws = ThisWorkbook.Sheets("Report Data")
    Set FilterRange = ws.Range("E1:EF" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)
    Set CopyRange = FilterRange.SpecialCells(xlCellTypeVisible)
    Set DestRange = Sheets("Sheet1").Range("A1")
    
        criteriaArray = Array("High", "Moderate-High")
    
    With ws
        .AutoFilterMode = False
        
        FilterRange.AutoFilter Field:=1, Criteria1:=criteriaArray,  _
        Operator:=xlFilterValues,
        Criteria2:="<>*Moderate*"
        
        
        
    End With
    
CopyRange.Copy DestRange

End Sub

Any and all help is greatly appreciated. Thanks!

D

 

[6StringJazzer]

You are missing a continuation character when you break to the last line

        FilterRange.AutoFilter Field:=1, Criteria1:=criteriaArray,  _
        Operator:=xlFilterValues, _
        Criteria2:="<>*Moderate*"

 

[Damian37]

You are missing a continuation character when you break to the last line

        FilterRange.AutoFilter Field:=1, Criteria1:=criteriaArray,  _
        Operator:=xlFilterValues, _
        Criteria2:="<>*Moderate*"

Thank you so much! I made the change, however, now the following lines highlight

.Range("E1:F").AutoFilter Field:=1, Criteria1:=criteriaArray, _
Operator:=xlFilterValues _
.Range("E1:F" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row).AutoFilter Field:=1, Criteria2:="<>*Moderate*", _
Operator:=xlFilterValues, _
.Range("E1:F" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row).AutoFilter Field:=1, Criteria2:="<>*Partial*"
And I receive a Run-time error '1004':
Method 'Range' of object '_Worksheet failed

With ws
        .AutoFilterMode = False
        
        .Range("E1:F").AutoFilter Field:=1, Criteria1:=criteriaArray, _
        Operator:=xlFilterValues, _
        Criteria2:="<>*MOderate*"
        '.Range("E1:F" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row).AutoFilter Field:=1, Criteria2:="<>*Moderate*", _
        Operator:=xlFilterValues, _
        '.Range("E1:F" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row).AutoFilter Field:=1, Criteria2:="<>*Partial*"

I commented out things I've been playing with, but nothing is working.

D.

 

[6StringJazzer]

Range("E1:F")

This is not a valid range. You need a number after the F.

 

[kevin9999]

In addition to Jeff's advice, I couldn't reproduce the problem you were having with excluding "Moderate" only. With this test code:

Sub No_Moderate()
    criteriaArray = Array("High", "Moderate-High")
    With Range("A1").CurrentRegion
        .AutoFilter 1, criteriaArray, 7
    End With
End Sub

This:

Book1
	A	B
1	HDR1	HDR2
2	High	data
3	Moderate	data
4	Moderate-High	data
5	High	data
6	Moderate	data
7	Moderate-High	data
Sheet3

Became this:

Book1
	A	B
1	HDR1	HDR2
2	High	data
4	Moderate-High	data
5	High	data
7	Moderate-High	data
8
Sheet3

In any event, if I can discern you intentions fully with your existing code, please try the following alternative on a copy of your workbook:

Option Explicit
Sub FilterMultipleCriteria()
    Dim ws As Worksheet, r As Range, criteriaArray

    'Add the new Sheet1
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet1").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Sheet1"
    
    'Set the data range & criteria
    Set ws = Worksheets("Report Data")
    Set r = ws.Range("E1:EF" & ws.Cells(Rows.Count, "E").End(xlUp).Row)
    criteriaArray = Array("High", "Moderate-High")
    
    'Filter & copy
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Copy Worksheets("Sheet1").Range("A1")
        End If
    End With
    ws.AutoFilter.ShowAllData
    
End Sub

 

[Damian37]

Range("E1:F")

This is not a valid range. You need a number after the F.

Hi

In addition to Jeff's advice, I couldn't reproduce the problem you were having with excluding "Moderate" only. With this test code:

Sub No_Moderate()
    criteriaArray = Array("High", "Moderate-High")
    With Range("A1").CurrentRegion
        .AutoFilter 1, criteriaArray, 7
    End With
End Sub

This:

Book1
	A	B
1	HDR1	HDR2
2	High	data
3	Moderate	data
4	Moderate-High	data
5	High	data
6	Moderate	data
7	Moderate-High	data
Sheet3

Became this:

Book1
	A	B
1	HDR1	HDR2
2	High	data
4	Moderate-High	data
5	High	data
7	Moderate-High	data
8
Sheet3

In any event, if I can discern you intentions fully with your existing code, please try the following alternative on a copy of your workbook:

Option Explicit
Sub FilterMultipleCriteria()
    Dim ws As Worksheet, r As Range, criteriaArray

    'Add the new Sheet1
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Sheet1").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Sheet1"
   
    'Set the data range & criteria
    Set ws = Worksheets("Report Data")
    Set r = ws.Range("E1:EF" & ws.Cells(Rows.Count, "E").End(xlUp).Row)
    criteriaArray = Array("High", "Moderate-High")
   
    'Filter & copy
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With r
        .AutoFilter 1, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            .Copy Worksheets("Sheet1").Range("A1")
        End If
    End With
    ws.AutoFilter.ShowAllData
   
End Sub

Thanks Kevin9999,
So, originally I was able to achieve the same results this code brought back. However, the Vulnerability column contains the rating "Moderate". That was the original issue. I've been trying to exclude any instances where the rating is "Moderate" and not "Moderate-High". For whatever reason when I attempt to write code to exclude the rating "Moderate", I receive the syntax error. I was using a different method to copy, but wherever I put code to exclude I would receive the error. I'm trying to add Criteria2:="<>*Moderate*", but haven't been able to figure out where to put it, or if this is the proper method to exclude to begin with. Can you see what I'm doing wrong? Thanks for any help!!!!

D.

 

[kevin9999]

Is "Moderate" in the same column as the other measures?

 

[Damian37]

Is "Moderate" in the same column as the other measures?

Yes. Each column contains the following measures: High, Moderate-High, Moderate, Low. I edited my code a little. This is the updated version:

Sub FilterMultipleCriteria()
'
 '   On Error Resume Next
    
' Filter by Multiple Criteria

    'Dim ws As Worksheet, r As Range, criteriaArray
'Add new Worksheet
 Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("Sheet1").Delete
  On Error GoTo 0
  Sheets.Add After:=Sheets(Sheets.Count)
  ActiveSheet.Name = "Sheet1"


    Dim ws As Worksheet
    Dim r As Range
    Dim FilterRange As Range
    Dim criteriaArray As Variant
    Dim CopyRange As Range
    Dim DestRange As Range
    Dim Cell As Range
    
    
    Set ws = Worksheets("Report Data")
    Set r = ws.Range("E1:EF" & ws.Cells(Rows.Count, "E").End(xlUp).Row)
    Set FilterRange = r.SpecialCells(xlCellTypeVisible)
    Set CopyRange = FilterRange
    Set DestRange = Sheets("Sheet1").Cells(1, 1)
    
        criteriaArray = Array("High", "Moderate-High", "<>*Moderate*")
    
'Filter & copy
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    With r
        .AutoFilter Field:=1, Criteria1:=criteriaArray, Operator:=xlFilterValues
            If .Resize(.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Resize(.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy DestRange
        '.SpecialCells(xlCellTypeVisible).Copy DestRange
            '.Copy Worksheets("Sheet1").Range("A1")
        End If
    End With
    ws.AutoFilter.ShowAllData
    Application.DisplayAlerts = True
        
End Sub

I'm now receiving an error '1004': AutoFilter method of Range class failed. This is the line that continues to be highlighted when I click "Debug": .AutoFilter Field:=1, Criteria1:=criteriaArray, Operator:=xlFilterValues. Do you see where I might be causing the error?

D.

 

[kevin9999]

OK, couple of things about your syntax. Firstly, if you want to apply 2 separate conditions on the same field, you need to have an xlAnd or xlOr Operator. So your code should look more like this:

.AutoFilter Field:=1, Criteria1:=Array("moderate-high", "high"), Operator:=xlAnd, Criteria2:="<>*moderate*"

Also, the second criteria will cancel out the first - excluding anything containing "Moderate" will exclude your desired "Moderate-High" - so you'll end up with neither.

If you could post your actual sheet using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform, it would be a big help.

 

[kevin9999]

Just saw you updated code. I really need to see your actual data to be able to assist further.