How do you go through each employee number and see if they have 2 codes to their ID?

[jojo52479]

I was given a project to go through each employee ID going back to the first employee and see when they switched from union to nonunion. Below is an example of my spreadsheet. Using VBA, what code would I be able to use to go through each EE# and determine when they switch to union codes 99, 87, and 88. Untimely, I want to only keep the EE# that went to those union codes and delete the EE that dont have these union codes. Any assistance could help. I have over 300K of lines so going one EE at a time wouldnt work.

EE#	Hire Date	Union Code
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	58C
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
14	7/10/1998	8
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	28C
25	3/11/1988	58C
25	3/11/1988	58C
25	3/11/1988	58C
25	3/11/1988	8
25	3/11/1988	8
25	3/11/1988	8
25	3/11/1988	8

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[jojo52479]

The error doesnt give a line. I just get a popup screen with the error.

 

[Fluff]

If you click the Debug button, it will highlight a line of code. Which line?

 

[jojo52479]

All the pop up gives me is OK and HELP. Usually you would get a debug for when I run this one, I dont.

 

[Fluff]

In that case put the cursor anywhere in the code & press F8, to step through the code line by line until you get an error.

 

[jojo52479]

The bold area turns yellow first.

Sub KeepUnionCode()
Dim Cl As Range
Dim Flg As Boolean

With CreateObject("scripting.dictionary")
For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
Select Case Cl.Offset(, 2).Value
Case 87, 88, 99: Flg = True
Case Else: Flg = False
End Select
If Not .exists(Cl.Value) Then
If Flg Then .Add Cl.Value, Nothing Else .Add Cl.Value, CStr(Cl.Value)
ElseIf Not IsEmpty(.Item(Cl.Value)) Then
If Flg Then .Item(Cl.Value) = Empty
End If
Next Cl
Range("A1").AutoFilter 1, .items, xlFilterValues
ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
Range("A1").AutoFilter
End With
End Sub

 

[Fluff]

Are you using a Mac?

 

[jojo52479]

No a PC

 

[Fluff]

In that case, try rebooting it.
If it still doesn't work in the VBE click Tools > references > scroll down & look to see if there is Microsoft Scripting Runtime. If it's there put a tick in the checkbox & just below you should see Location & Standard. Do you have details there?

 

[jojo52479]

I rebooted and same thing.

The location: c:\windows\SysWOW64\scrrun.dll
The Language: Standard

 

[Fluff]

In that case I'm not sure.
It maybe that something on your system is corrupted.