PDA

View Full Version : If then, Go To Next Sub using a filtered column in VBA



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

mdmackillop
06-18-2016, 04:05 PM
Why not simply do a Find on the column. If found then Autofilter.

sschwant
06-19-2016, 06:38 AM
Why not simply do a Find on the column. If found then Autofilter.

What would that code logic / syntax look like? Something like this?



Col Q, Find "Xfers"

If Col Q = "", Then Go To Next Sub

Else Auto Filter (rest of my sub runs as is)

End If

End Sub

Thanks!

Steve

mdmackillop
06-19-2016, 06:52 AM
Simplest to do it from another sub

Sub test()
Dim c As Range
Set c = Range("$A$1:$R$4062").Find("xfers", lookat:=xlPart)
If c Is Nothing Then
Call OtherMacro
Else
Call Move_Xfers_Out
End If
End Sub

snb
06-20-2016, 02:24 AM
Use advancedfilter, it's been designed for this purpose.