sschwant
06-18-2016, 02:57 PM
I have a macro with a 2 step process. 1) filter on Description col for any "AP Accruals", 2) filter on same col for any "Xfers". While there will always be AP Accruals, there will be times when there will not be any "Xfers" in the data. I need to figure out the right syntax or logic to say IF my Autofilter for Xfers is blank or "", then skip the rest of that routine and move onto the next one. B/c if it goes on as if there were some data there it screws up the rest of process (it deletes the 1st row of column headers, moves remaining rows up 1 in source data and then skips the first row of data (needed) in my final step where I move data to a new tab. This process works fine both step 1 and step 2 - IF the filter identifies any criteria in the column. This could an IF / End IF, and IF THEN, End IF or IF GO TO. Just not sure how to set up the block of code to say IF my filter is blank, then go to next sub or bottom of current sub . . . and skip the copy paste routine. Here's the key section of the code:
Sub Move_Xfers_Out()
Application.ScreenUpdating = True
Sheets("Stage raw JE data").Select
Range("Q1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$4062").AutoFilter Field:=17, Criteria1:= _
"=*Xfers*", Operator:=xlAnd
If Criteria1 = "*" Then GoTo 10
End If
ActiveCell.Offset(1, -16).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
' Switch to IC Transfers tab
Sheets("IC Transfers").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Stage raw JE data").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter
10: Call Copy_Stgd_JE_data_to_TblJE
End Sub
Thanks in advance for any help!!!
Tried using 10: as the identifier for the line for the GoTo statement .... but this is way off the mark I'm sure ... the issue seems to be finding the right syntax and right way to structure it around the filter ....
Steve
cross posted: http://www.mrexcel.com/forum/excel-questions/948179-if-filter-result-null-then-go-next-step-sub-within-macro.html#post4554714
and here: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-to-redirect-a-macro-if-an-auto-filter-criteria/3cd539e6-de80-4646-9d76-7a6aa9acd136?tm=1466288572575
Sub Move_Xfers_Out()
Application.ScreenUpdating = True
Sheets("Stage raw JE data").Select
Range("Q1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$4062").AutoFilter Field:=17, Criteria1:= _
"=*Xfers*", Operator:=xlAnd
If Criteria1 = "*" Then GoTo 10
End If
ActiveCell.Offset(1, -16).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
' Switch to IC Transfers tab
Sheets("IC Transfers").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Stage raw JE data").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter
10: Call Copy_Stgd_JE_data_to_TblJE
End Sub
Thanks in advance for any help!!!
Tried using 10: as the identifier for the line for the GoTo statement .... but this is way off the mark I'm sure ... the issue seems to be finding the right syntax and right way to structure it around the filter ....
Steve
cross posted: http://www.mrexcel.com/forum/excel-questions/948179-if-filter-result-null-then-go-next-step-sub-within-macro.html#post4554714
and here: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-to-redirect-a-macro-if-an-auto-filter-criteria/3cd539e6-de80-4646-9d76-7a6aa9acd136?tm=1466288572575