Macro to autofilter data then add value to cells within table based on filtered criteria

Hi,

Really looking for some assistance so thank you in advance !!

I have a table (cost) of data on sheet "Raw Data" starting in row 2 , I'm trying to run through a series of filters and apply a value to column BE based on the filtered data.

The issue I have if the filter returns no data the macro stops and doesn't move onto the next filter criteria.

The macro has about 10 different filter criterias, the below is just an example

Sub Scheme()

'BMW

Sheets("Raw Data").Select
ActiveSheet.ListObjects("Cost").AutoFilter.ShowAllData

ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=3, Criteria1:= _
"=BMW", Operator:=xlOr, Criteria2:="=MINI"
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=11, Criteria1:= _
"=*MW*", Operator:=xlAnd
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=57, Criteria1:= _
"=BMW", Operator:=xlAnd

Dim c As Excel.Range

For Each c In Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

If c.Offset(, -1).Value = vbNullString Then Exit For
c.Value = "BMW-Scheme"

Next

ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=57, Criteria1:= _
"<>BMW-Scheme", Operator:=xlAnd
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=58, Criteria1:= _
"=B*", Operator:=xlAnd

For Each c In Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

If c.Offset(, -1).Value = vbNullString Then Exit For
c.Value = "BMW-Scheme"

Next

Sheets("Raw Data").Select
ActiveSheet.ListObjects("Cost").AutoFilter.ShowAllData
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=3, Criteria1:= _
"=BMW", Operator:=xlOr, Criteria2:="=MINI"
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=57, Criteria1:= _
"=BMW", Operator:=xlAnd

For Each c In Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

If c.Offset(, -1).Value = vbNullString Then Exit For
c.Value = "BMW-Non-Scheme"

Next

End Sub

It would help a lot if you attach a file with some unpersonalised data

Erwin

Hi,
I'm really stuck, I need the macro to filter to criteria 1 then overwrite the applicable cells with certain text then move onto the next filter criteria and overwrite with a different text, if the filter criteria returns no data I need it to move onto the next filter criteria etc.

Sub Scheme()

Sheets("Raw Data").Select
ActiveSheet.ListObjects("Cost").AutoFilter.ShowAllData

ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=3, Criteria1:= _
"=BMW", Operator:=xlOr, Criteria2:="=MINI"
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=11, Criteria1:= _
"=*MW*", Operator:=xlAnd
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=57, Criteria1:= _
"=BMW", Operator:=xlAnd

Dim c As Excel.Range

If Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible) > 0 Then

For Each c In Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

c.Value = "BMW-Scheme"

Next

ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=57, Criteria1:= _
"<>BMW-Scheme", Operator:=xlAnd
ActiveSheet.ListObjects("Cost").Range.AutoFilter Field:=58, Criteria1:= _
"=B*", Operator:=xlAnd

If Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible) > 0 Then

For Each c In Range("be3:be" & Range("be" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

c.Value = "BMW-Non-Scheme"

End With

End Sub

With attachment

Hi Erwin,

That is perfect !!! Thank you very very much.

Kind Regards Rob

Hi,

Weird running it this morning it errors as there are no cells visible post the first filter criteria

Any help would be really appreciated as really struggling with this