Using If Function in VBA for different conditions and Different criteria

[arpitpatel0101]

I have some data which has three values: Pass/Rejected/On Hold, Now I want to save only pass data in Different sheet,
but there is one condition, If There is no pass data, It shows msgbox "there is no Pass data", If only one Pass data found, it would save in 'sheet 2' and if more than one Pass Data found, it would save in 'sheet 2'.
Please help me in these.
I have tried these, it's works, codes are correct for 'pass_more_than_one' and 'pass_only_one', I think some thing wrong with If function only, so please suggest.

Sub TICKLER_AR()
 
Dim r As Range 
Dim j As Integer
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'Tickler Working
Set r = Range(Range("A6"), Range("AF6").End(xlDown))
Sheets("Settlement").Range("5:5").AutoFilter
Range("AF1").Select
ActiveSheet.Range("A:AM").AutoFilter Field:=32, Criteria1:="Pass Waiver"
 
On Error Resume Next ' If the lookup fails, ignore the error
    ' the following assumes Part is in Column A
    j = WorksheetFunction.CountA(r.Cells.SpecialCells(xlCellTypeVisible))
On Error GoTo 0 ' Resume normal error handling
 
If j = 0 Then
MsgBox "There is no Pass Waiver Data..!"
 
ElseIf j <> 1 Then
Call pass_only_one
 
Else
Call pass_more_than_one
End If
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

[Luuk]

Looking at line #23 it seems to me, if I understand what your problem is that this should be:

else if j=1 then

But maybe you should alo look for the CASE statement
(http://www.techonthenet.com/excel/formulas/case.php)