sideburnsurfer79
New Member
- Joined
- Mar 8, 2010
- Messages
- 7
Hi there, I've been trying to solve this for 2 days now and am at the point of banging my head against the wall. My VBA skills are limited and most macros that I've written/recorded come from trial and error and looking things up on these types of forum but this time, I'm stuck.
What I want to do seems simple in my head but I just can't get it to work.
I have a code that needs to do a number of autofilters from Column A and type some text into Column C and autofill. This bit works with no problems at all. However, when it tries to filter something that is not found, I get an error but am unable to 'get round it'.
I'd like it to clear the filter and start again from the beginning of the next loop
I have tried something like this...but am failing completely. Any ideas gratefully received.
I have tried to succeed with some variation of On Error that I found but it either errors or goes into an eternal loop before crashing. I've been using it like the below which is obviously incorrect but I'm stumped.
Please help.
What I want to do seems simple in my head but I just can't get it to work.
I have a code that needs to do a number of autofilters from Column A and type some text into Column C and autofill. This bit works with no problems at all. However, when it tries to filter something that is not found, I get an error but am unable to 'get round it'.
I'd like it to clear the filter and start again from the beginning of the next loop
I have tried something like this...but am failing completely. Any ideas gratefully received.
HTML:
ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*bhe*", _
Operator:=xlAnd
'If it can't find anything matching the criteria above, I want it to skip to....
Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "Blip Header"
Selection.FillDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 153, 255)
End With
'here.This way it should clear it and start the next loop part of filtering.
ActiveSheet.Range("$A$1:$O$307").AutoFilter Field:=1
ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*ghe*", _
Operator:=xlAnd
Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "Gondola Header"
Selection.FillDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 204, 255)
End With
ActiveSheet.Range("$1:$307").AutoFilter Field:=1
ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*tke*", _
Operator:=xlAnd
Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "Talker"
Selection.FillDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(153, 204, 255)
End With
ActiveSheet.Range("$1:$307").AutoFilter Field:=1
I have tried to succeed with some variation of On Error that I found but it either errors or goes into an eternal loop before crashing. I've been using it like the below which is obviously incorrect but I'm stumped.
HTML:
On Error GoTo ErrHandler:
ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*bhe*", _
Operator:=xlAnd
Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "Blip Header"
Selection.FillDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 153, 255)
End With
Label1:
Exit Sub
ErrHandler:
Resume Label1:
ActiveSheet.Range("$A$1:$O$307").AutoFilter Field:=1
ActiveSheet.Range("$1:$307").AutoFilter Field:=1, Criteria1:="=*ghe*", _
Operator:=xlAnd
Range([c2], Cells(Rows.Count, "C")).SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "Gondola Header"
Selection.FillDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 204, 255)
End With
ActiveSheet.Range("$1:$307").AutoFilter Field:=1
Please help.