Set specific user with column user-type

Hi All,

1. I add a new column and name. My new column name is User-type and custom_field: Employee ID. But I don't know how to set the specific user with their criteria based on user-type.

a. My requirement for column "User-type" is I want employee id 00012 as an admin, employee id 00017 as a lecturer and another employee id is a student

Thank you for your help.

Sub Filter2()

Dim wb As Workbook
Dim ws As Worksheet
Dim RangeToFilter As Range
Dim CriteriaCell As Range
Dim CriteriaWildCard As String
Dim DestinationRange As Range

Set wb = ThisWorkbook
'### change back
Set ws = wb.Worksheets(1)
Set RangeToFilter = ws.Range("A:O") 'existing coloumn
Set CriteriaCell = ws.Range("R2")
Set DestinationRange = ws.Range("T1:AL1") 'new destinatination column


'CriteriaWildCard = CriteriaCell & "*"
'## was I
CriteriaWildCard = ">=" & Format(Date, ws.Range("J2").NumberFormat)
'Range("AE1,AF1").NumberFormat = "mm/dd/yyyy"

ws.Range("R:AL").Clear



' filter for date after today and status
With RangeToFilter
    .AutoFilter field:=10, Criteria1:=CriteriaWildCard
    .AutoFilter field:=9, Criteria1:="=Active"
   
End With
' copy only visible cells
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=DestinationRange
ws.Range("T1") = "A_Login"
ws.Range("U1") = "B_custom_field: Gender"
ws.Range("V1") = "C_Firstname"
ws.Range("W1") = "D_Lastname"
ws.Range("X1") = "E_custom_field: Position"
ws.Range("Y1") = "F_custom_field: Category"
ws.Range("Z1") = "G_custom_field: Department"
ws.Range("AA1") = "H_Location"
ws.Range("AB1") = "I_Active"
ws.Range("AC1") = "J_custom_field: Last day of work"
ws.Range("AD1") = "K_custom_field: Date Joined"
ws.Range("AE1") = "L_custom_field: Line Manager"
ws.Range("AF1") = "M_Email"
ws.Range("AG1") = "N_Expat/Inpat_1"
ws.Range("AH1") = "O_Job_DeptDescr_1"



' clear filter
RangeToFilter.AutoFilter

' change Payroll status
Dim LRow As Long
'ws.Range("A1").CurrentRegion.Copy Destination:=DestinationRange
LRow = ws.Cells(Rows.count, 29).End(xlUp).Row

For i = 2 To LRow

    If ws.Cells(i, 28) = "Active" Then
        ws.Cells(i, 28) = "Yes"
        Else: ws.Cells(i, 28) = "No"
    End If
Next i



' change gender
Dim LastRow4 As Long
LastRow4 = ws.Cells(Rows.count, 22).End(xlUp).Row

    For i = 2 To LastRow4
    If ws.Cells(i, 21) = "Cik" Then
        ws.Cells(i, 21) = "Female"
        ElseIf ws.Cells(i, 21) = "Puan" Then
        ws.Cells(i, 21) = "Female"
        Else: ws.Cells(i, 21) = "Male"
    End If
Next i

' add "MY" to create Login
Dim LRow2 As Long

LRow2 = ws.Cells(Rows.count, 20).End(xlUp).Row

For i = 2 To LRow2
    ws.Cells(i, 20) = "MY" & ws.Cells(i, 20)
Next i



'insert new column and name
Range("X1:Y1").EntireColumn.Insert
Dim name: name = Split("User-type,custom_field: Employee ID", ",")
Sheet1.Range("X1").Resize(1, UBound(name) + 1) = name



' autofit all columns
ws.Columns("A:AJ").AutoFit


End Sub