What am i doint wrong?? VBA If (X <> "A" or X<> "B") And Y="" Then

[powercell99]

Hi, I've been banging my head against the wall trying to determine what i havewrong with my code. I have an employee roster (Sheet1) and I'm trying to get the code to look through Sheet1 and if the Last Name field is NOT "Vacant" or "Recruit" , and the Emp ID Field is not blank, then list it on Sheet2.

But the result pulls in many Vacant and Recruit records when they should be excluded. What am i doing wrong????

Here is my code, any recommendations would be GREEEEAAATTTLYYYY appreciated! I know its a bit sloppy, but i'm a novice.

Sub Test()
'
Dim WSRDL As Worksheet
Dim WSQC As Worksheet
 
Dim LastRow As Long
Dim LastRowQC As Long
 
 
Set WSRDL = Worksheets("Sheet1")
Set WSQC = Worksheets("Sheet2")
 
LastRow = (WSRDL.Range("A20000").End(xlUp).Row) - 4
LastRowQC = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
Debug.Print LastRow
 
Application.ScreenUpdating = False
 
 
WSRDL.Activate
 
Range("A1").Select
For RowNum = 2 To LastRow
 
Dim EmpID As String
Dim LastName As String
Dim FirstName As String
 
LastName = Sheets("Sheet1").Cells(RowNum, 1)
EmpID = Sheets("Sheet1").Cells(RowNum, 24)
 
If (LastName <> "Vacant" Or LastName <> "Recruit") And EmpID = "" Then
        WSQC.Cells(LastRowQC, 1).Value = 16
        WSQC.Cells(LastRowQC, 5).Value = EmpID
        WSQC.Cells(LastRowQC, 6).Value = LastName
        WSQC.Cells(LastRowQC, 12).Value = "Emp ID is Blank"
       
        Else
        End If
 
LastRowQC = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
 
Next RowNum
 
Range("A1").Select
 
End Sub

Thanks so much for looking.

 

[Comfy]

You need to use the AND operator rather than OR.

With OR only one condition needs to be TRUE.

So if the Last name is "Vacant" the OR will evaluate (FALSE, TRUE) and return TRUE, "Vacant" will be imported as it doesn't equal "Recruit"

For AND all conditions need to be TRUE.

So if the Last name is Vacant the AND will evaluate (FALSE, TRUE) and return FALSE.

 

[Comfy]

I used to do the same because in my Head I would say:


If Lastname <> "Recruit" OR "Vacant"

And because I thought of OR I would assume OR is correct.

 

[powercell99]

WOW, That make perfect sense now that you wrote it. I can't believe i didnt see it before. That is why i LOVE this board!!!! Thanks for the very fast reply. It helps me out alot!

 

[Comfy]

WOW, That make perfect sense now that you wrote it. I can't believe i didnt see it before. That is why i LOVE this board!!!! Thanks for the very fast reply. It helps me out alot!

You're welcome.