aravindhan_31
Well-known Member
- Joined
- Apr 11, 2006
- Messages
- 672
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I have recorded the below macro for filtering the values in column 35 with Value contains "Canc" and then filter col B & C with blanks then fill in all the blank cells Col B & C as "To be Cancelled" and "Invalid" respectively
once this is done again the it filters in Column 16 with Credit and then fill in all the blank cells Col B & C as "Credit" and "Credit" respectively.
the code works fine if there are results for the criteria "Canc" & Credit
but if no results exists for any one then I get an error message on the line
and the macro stops
the reason fo this is when there is no results there will not be any blank cells, so macro cant select.
how do I change this to work if no result just skip it and proceed with the next one.
Thanks for your help
once this is done again the it filters in Column 16 with Credit and then fill in all the blank cells Col B & C as "Credit" and "Credit" respectively.
Code:
sub fill_blanks
Sheets("Unique").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
'Field 35
ActiveSheet.Range("A1:AS" & LR).AutoFilter Field:=35, Criteria1:= _
"=*Canc*", Operator:=xlAnd
ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=2, Criteria1:="="
ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=3, Criteria1:="="
Range("$B$1:B" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "To be Cancelled"
Range("$C$1:C" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "Invalid"
Selection.AutoFilter
Range("B1").Select
' Field 16
ActiveSheet.Range("A1:AS" & LR).AutoFilter Field:=16, Criteria1:= _
"=*Credit*", Operator:=xlAnd
ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=2, Criteria1:="="
ActiveSheet.Range("$A$1:AS" & LR).AutoFilter Field:=3, Criteria1:="="
Range("$B$1:B" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "Credit"
Range("$C$1:C" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "Credit"
Selection.AutoFilter
Range("B1").Select
but if no results exists for any one then I get an error message on the line
Code:
Selection.SpecialCells(xlCellTypeBlanks).Select
the reason fo this is when there is no results there will not be any blank cells, so macro cant select.
how do I change this to work if no result just skip it and proceed with the next one.
Thanks for your help