Malicious Office (OOXML) — malware analysis report

Static analysis result for SHA-256 5ee45c30e6510a2d…

MALICIOUS

Office (OOXML)

499.8 KB Created: 2020-08-19 17:56:47 UTC Authoring application: Microsoft Excel 16.0300 First seen: 2021-02-09
MD5: 528ca60575c7f3c5404268633060acc6 SHA-1: e7b1de5e61a8a4bdd29dc90b902941a7aebd44e5 SHA-256: 5ee45c30e6510a2d074c477ba3dc1b700f635e6d1590ee160e4e4f889d52b104
274 Risk Score

Malware Insights

MITRE ATT&CK
T1566.001 Spearphishing Attachment T1059.005 Visual Basic T1059.001 PowerShell T1105 Ingress Tool Transfer

The sample is an Excel document containing VBA macros that impersonates a cloud document lure. The Workbook_Open macro executes a series of functions, including 'DownloadEmployeeMasterData', which likely downloads and executes a second-stage payload. This is supported by the 'OLE_VBA_BITSTRANSFER_DROPPER' heuristic firing, indicating a download-and-run command.

Heuristics 11

  • VBA project inside OOXML medium 6 related findings OOXML_VBA
    Document contains a VBA project — VBA macros present
  • WScript.Shell usage critical OLE_VBA_WSCRIPT
    WScript.Shell usage
    Matched line in script
        Set wshShell = CreateObject("Wscript.Shell")
  • VBA stages a PowerShell/LOLBin download-and-run command critical OLE_VBA_BITSTRANSFER_DROPPER
    The macro assembles a download command using a PowerShell or LOLBin download primitive (Start-BitsTransfer, Invoke-WebRequest, Net.WebClient, bitsadmin, certutil, ...) that fetches a remote payload, then executes it -- writing it to a script file and running it, or launching it directly from an auto-exec handler. The keywords are commonly split with PowerShell backtick / cmd caret escapes to evade scanners; this detection de-escapes the source first. A high-confidence downloader/dropper, stronger than the individual Shell / download keywords on their own.
    Matched line in script
    Private Sub Workbook_Open()
  • CreateObject call high OLE_VBA_CREATEOBJ
    CreateObject call
    Matched line in script
            Set networkPath = CreateObject("WScript.Network")
  • VBA p-code auto-exec with execution tokens high OLE_VBA_PCODE_AUTOEXEC_EXEC
    Triggers on the COMBINATION of two tokens co-occurring in the same compiled VBA/cache stream: an auto-execution entry point (Auto_Open / AutoOpen / Document_Open / Workbook_Open / Auto_Close / AutoClose) AND a shell/download/object-execution token (Shell, CreateObject, GetObject, PowerShell, cmd.exe, URLDownloadToFile, WinHttp, XMLHTTP, ADODB.Stream, ShellExecute, ExecuteExcel4Macro). Neither token alone fires it — it is the pairing that flags p-code-only or source-extraction-failure macro documents where the visible VBA source is unavailable. The matched tokens are named in the detail line below.
  • Workbook_Open macro low OLE_VBA_WBOPEN
    Workbook_Open macro
    Matched line in script
    Private Sub Workbook_Open()
  • Environ() call (env variable access) low OLE_VBA_ENVIRON
    Environ() call (env variable access)
    Matched line in script
        scriptFullFileName = Environ("Temp") & "\" & "Config"
  • Cloud document impersonation lure medium SE_CLOUD_DOC_LURE
    Document impersonates a cloud file-sharing service such as SharePoint, OneDrive, Google Drive, Dropbox, Box, or Microsoft 365 and asks the user to open, verify, or access a shared document
  • External hyperlinks (16) low OOXML_EXTERNAL_HYPERLINKS
    Document contains 16 external hyperlinks — clickable URLs are stored as external relationships. First target: https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG6/Shared Documents/Edwards PG 6
  • Hidden worksheet (hidden) low OOXML_HIDDEN_SHEET
    Excel workbook contains 6 hidden sheet(s) — hidden sheets are commonly used to conceal macro code, staging data, or intermediate payload construction
  • Embedded URL info EMBEDDED_URL
    One or more URLs were extracted from the document. The URL itself is not a detection — see the per-URL labels for which channel (macro, JS, link annotation, document body, ...) reached each URL.
    URL https://docs.google.com/spreadsheets/d/e/2PACX-1vQXt8FJxvsvO1aZADI1MKJfUcLtJAFZO7c-XKIc6j9JCizV8a7yborS3AaasiLLBA/pubhtml Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/PayRunPreparation/Shared%20Documents/MasterIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG3/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG1/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG2/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG3/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG4/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG5/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG6/SharedDocument hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG7/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG8/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG1/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG2/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG4/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG5/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG6/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG72/SharedIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG6/Shared Documents/Edwards PG 6Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG4/Shared Documents/Compressor PG 4Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG1/Shared Documents/Edwards PG 1Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG5/Shared Documents/Edwards PG 5Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG2/Shared Documents/Compressor PG 2Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG3/Shared Documents/Compressor PG 3Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG72/Shared Documents/Compressor PG 7Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/PayRunPreparation/Shared Documents/Master DataDocument hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG4/Shared Documents/Edwards PG 4Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG1/Shared Documents/Compressor PG 1Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG3/Shared Documents/Edwards PG 3Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG6/Shared Documents/Compressor PG 6Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG8/Shared Documents/Edwards PG 8Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG2/Shared Documents/Edwards PG 2Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-EdwardsPG7/Shared Documents/Edwards PG 7Document hyperlink
    • https://onevirtualoffice.sharepoint.com/sites/ACNAPayroll-CompressorPG5/Shared Documents/Compressor PG 5Document hyperlink
    • https://support.microsoft.com/en-us/help/2745652/object-model-calls-may-fail-from-workbookopen-event-when-exiting-proteIn document text (OOXML body / shared strings)
    • https://onevirtualoffice.sharepoint.com/sites/PayRunPreparation/Shared%20Documents/Temp%20Folder/In document text (OOXML body / shared strings)

Extracted artifacts 15

Files carved from inside the sample during analysis.

FilenameKindSourceSize
macros.bas vba-macro oletools.olevba.extract_macros (decoded VBA source from OOXML) 122526 bytes
SHA-256: e35409abeb35a1dca449b1613b79e27a59fa6e6964b3b7c7febfaaa0c362ddb0
Preview script
First 1,000 lines of the extracted script
Attribute VB_Name = "ThisWorkbook"
Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Private Sub Workbook_Open()


    On Error GoTo AnyError
        
        VBConfig
        Connect

        UnlockEmployeeMasterSheet
        DownloadEmployeeMasterData
        LockEmployeeMasterSheet
            
        Exit Sub
    '   --------
    
AnyError:
        MsgBox "Macro failed to complete." & vbNewLine & vbNewLine & Err.Description
        
End Sub

Attribute VB_Name = "Sheet14"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Attribute VB_Control = "OptionButton7, 17, 7, MSForms, OptionButton"
Attribute VB_Control = "OptionButton5, 15, 8, MSForms, OptionButton"
Attribute VB_Control = "OptionButton4, 14, 9, MSForms, OptionButton"
Attribute VB_Control = "OptionButton1, 3, 10, MSForms, OptionButton"
Attribute VB_Control = "OptionButton2, 4, 11, MSForms, OptionButton"
Attribute VB_Control = "OptionButton3, 5, 12, MSForms, OptionButton"
Attribute VB_Control = "OptionButton12, 22, 13, MSForms, OptionButton"
Attribute VB_Control = "OptionButton16, 26, 14, MSForms, OptionButton"
Attribute VB_Control = "OptionButton17, 27, 15, MSForms, OptionButton"
Attribute VB_Control = "OptionButton18, 28, 16, MSForms, OptionButton"
Attribute VB_Control = "OptionButton19, 29, 17, MSForms, OptionButton"
Attribute VB_Control = "OptionButton20, 30, 18, MSForms, OptionButton"
Attribute VB_Control = "OptionButton6, 31, 19, MSForms, OptionButton"
Private Sub OptionButton3_Click()

'    Dim rng As Range
'    Set rng = ActiveSheet.Range("C9")
'    With ActiveSheet.OLEObjects("OptionButton18")
'        .Top = rng.Top
'        .Left = rng.Left
'        .Width = rng.Width
'        .Height = rng.RowHeight
'        .Visible = True
'
'    End With
'
'
'    ThisWorkbook.Worksheets("Ctrl").OptionButton4.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton5.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton19.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton20.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton16.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton17.Visible = False
'    ThisWorkbook.Worksheets("Ctrl").OptionButton12.Visible = False
'
'    ThisWorkbook.Worksheets("Ctrl").OptionButton18.Value = True
'
    
    ' AC Compressors has 5 channels
    ThisWorkbook.Worksheets("Ctrl").OptionButton4.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton5.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton18.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton19.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton20.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton16.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton17.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton12.Visible = False

    ThisWorkbook.Worksheets("Ctrl").OptionButton4.Value = True

    Dim rng1 As Range
    Set rng1 = ActiveSheet.Range("C9")
    With ActiveSheet.OLEObjects("OptionButton4")
        .Top = rng1.Top
        .Left = rng1.Left
        .Width = rng1.Width
        .Height = rng1.RowHeight
        .Visible = True
    End With

    Dim rng2 As Range
    Set rng2 = ActiveSheet.Range("C11")
    With ActiveSheet.OLEObjects("OptionButton5")
        .Top = rng2.Top
        .Left = rng2.Left
        .Width = rng2.Width
        .Height = rng2.RowHeight
        .Visible = True
    End With
    
    Dim rng3 As Range
    Set rng3 = ActiveSheet.Range("C13")
    With ActiveSheet.OLEObjects("OptionButton18")
        .Top = rng3.Top
        .Left = rng3.Left
        .Width = rng3.Width
        .Height = rng3.RowHeight
        .Visible = True
    End With
  
    
    Dim rng5 As Range
    Set rng5 = ActiveSheet.Range("C15")
    With ActiveSheet.OLEObjects("OptionButton19")
        .Top = rng5.Top
        .Left = rng5.Left
        .Width = rng5.Width
        .Height = rng5.RowHeight
        .Visible = True
    End With

    Dim rng6 As Range
    Set rng6 = ActiveSheet.Range("C17")
    With ActiveSheet.OLEObjects("OptionButton20")
        .Top = rng6.Top
        .Left = rng6.Left
        .Width = rng6.Width
        .Height = rng6.RowHeight
        .Visible = True
    End With
    
    Dim rng7 As Range
    Set rng7 = ActiveSheet.Range("C19")
    With ActiveSheet.OLEObjects("OptionButton16")
        .Top = rng7.Top
        .Left = rng7.Left
        .Width = rng7.Width
        .Height = rng7.RowHeight
        .Visible = True
    End With

    Dim rng8 As Range
    Set rng8 = ActiveSheet.Range("C21")
    With ActiveSheet.OLEObjects("OptionButton17")
        .Top = rng8.Top
        .Left = rng8.Left
        .Width = rng8.Width
        .Height = rng8.RowHeight
        .Visible = True
    End With
'
'    Dim rng9 As Range
'    Set rng9 = ActiveSheet.Range("C23")
'    With ActiveSheet.OLEObjects("OptionButton12")
'        .Top = rng9.Top
'        .Left = rng9.Left
'        .Width = rng9.Width
'        .Height = rng9.RowHeight
'        .Visible = False
'    End With
    
    
    'Remove colour on cells
    Range("G9:G26").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    ThisWorkbook.Worksheets("Ctrl").Range("H9") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("H11") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("H13") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("H15") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G9") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G11") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G13") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G15") = ""
    
    Range("A7").Select
    

End Sub

Private Sub OptionButton7_Click()

    ThisWorkbook.Worksheets("Ctrl").OptionButton4.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton5.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton18.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton19.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton20.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton16.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton17.Visible = True
    ThisWorkbook.Worksheets("Ctrl").OptionButton12.Visible = True

    ThisWorkbook.Worksheets("Ctrl").OptionButton4.Value = True

    Dim rng1 As Range
    Set rng1 = ActiveSheet.Range("C9")
    With ActiveSheet.OLEObjects("OptionButton4")
        .Top = rng1.Top
        .Left = rng1.Left
        .Width = rng1.Width
        .Height = rng1.RowHeight
        .Visible = True
    End With

    Dim rng2 As Range
    Set rng2 = ActiveSheet.Range("C11")
    With ActiveSheet.OLEObjects("OptionButton5")
        .Top = rng2.Top
        .Left = rng2.Left
        .Width = rng2.Width
        .Height = rng2.RowHeight
        .Visible = True
    End With
    
    Dim rng3 As Range
    Set rng3 = ActiveSheet.Range("C13")
    With ActiveSheet.OLEObjects("OptionButton18")
        .Top = rng3.Top
        .Left = rng3.Left
        .Width = rng3.Width
        .Height = rng3.RowHeight
        .Visible = True
    End With
  
    
    Dim rng5 As Range
    Set rng5 = ActiveSheet.Range("C15")
    With ActiveSheet.OLEObjects("OptionButton19")
        .Top = rng5.Top
        .Left = rng5.Left
        .Width = rng5.Width
        .Height = rng5.RowHeight
        .Visible = True
    End With

    Dim rng6 As Range
    Set rng6 = ActiveSheet.Range("C17")
    With ActiveSheet.OLEObjects("OptionButton20")
        .Top = rng6.Top
        .Left = rng6.Left
        .Width = rng6.Width
        .Height = rng6.RowHeight
        .Visible = True
    End With
    
    Dim rng7 As Range
    Set rng7 = ActiveSheet.Range("C19")
    With ActiveSheet.OLEObjects("OptionButton16")
        .Top = rng7.Top
        .Left = rng7.Left
        .Width = rng7.Width
        .Height = rng7.RowHeight
        .Visible = True
    End With

    Dim rng8 As Range
    Set rng8 = ActiveSheet.Range("C21")
    With ActiveSheet.OLEObjects("OptionButton17")
        .Top = rng8.Top
        .Left = rng8.Left
        .Width = rng8.Width
        .Height = rng8.RowHeight
        .Visible = True
    End With
    
    Dim rng9 As Range
    Set rng9 = ActiveSheet.Range("C23")
    With ActiveSheet.OLEObjects("OptionButton12")
        .Top = rng9.Top
        .Left = rng9.Left
        .Width = rng9.Width
        .Height = rng9.RowHeight
        .Visible = True
    End With


'Remove the colour on cells
    Range("G9").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("G11").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("G13").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("G15").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    ThisWorkbook.Worksheets("Ctrl").Range("H9") = "Maximum Overtime (OT)"
    ThisWorkbook.Worksheets("Ctrl").Range("H11") = "Maximum Doubletime (DT)"
    ThisWorkbook.Worksheets("Ctrl").Range("H13") = "Minimum hours weekly"
    ThisWorkbook.Worksheets("Ctrl").Range("H15") = "Minimum hours bi-weekly"
    ThisWorkbook.Worksheets("Ctrl").Range("G9") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G11") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G13") = ""
    ThisWorkbook.Worksheets("Ctrl").Range("G15") = ""
    
    Range("A7").Select

End Sub

Attribute VB_Name = "SheetConfigScript"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True
Option Explicit


Attribute VB_Name = "Sheet11"
Attribute VB_Base = "0{00020820-0000-0000-C000-000000000046}"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Attribute VB_TemplateDerived = False
Attribute VB_Customizable = True

Attribute VB_Name = "Checkfile"
Sub prototype()

Dim ws As Worksheet
Dim wb As Workbook

'Dim OptionButton1 As Variant
Dim LastRow As Long
Dim LastRow2 As Long
Dim val As Integer
Dim CompId As String
'Dim wb As Workbook
Dim wsCtrl As Worksheet
Dim NumberOfLoop As Long


LOG.sub_ClearLog

'LOG.sub_Log Activity1:="Prototype", Activity2:="Start"
Application.StatusBar = ""

On Error GoTo AnyError
    
' Start recording summary data, including elapsed time
CallScript "SummaryData_Initialize"

Set wb = ThisWorkbook
Set wsCtrl = wb.Worksheets("Ctrl")
Set wsData = wb.Worksheets("Data")



'Check Comp ID

If ThisWorkbook.Worksheets("Ctrl").OptionButton3.Value = True Then
    CompId = ThisWorkbook.Worksheets("Ctrl").OptionButton3.Caption  'AC Compressors
    HeaderColumn = "Yes"
    EmployeeMasterCOMPID = "ATLASCOI"
    
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton7.Value = True Then
    CompId = ThisWorkbook.Worksheets("Ctrl").OptionButton7.Caption  'Edwards
    HeaderColumn = "No"
    EmployeeMasterCOMPID = "ATLASEDW"

End If


If ThisWorkbook.Worksheets("Ctrl").OptionButton4.Value = True Then
    ChannelPicked = 1
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton5.Value = True Then
    ChannelPicked = 2
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton18.Value = True Then
    ChannelPicked = 3
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton19.Value = True Then
    ChannelPicked = 4
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton20.Value = True Then
    ChannelPicked = 5
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton16.Value = True Then
    ChannelPicked = 6
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton17.Value = True Then
    ChannelPicked = 7
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton12.Value = True Then
    ChannelPicked = 8
    
End If


'set pay frequency for channels in Edwards
If ThisWorkbook.Worksheets("Ctrl").OptionButton7.Value = True Then
    If (ChannelPicked = 1 Or ChannelPicked = 2 Or ChannelPicked = 5) Then
        PayFrequency = "Weekly"
    Else
        PayFrequency = "Bi-Weekly"
    End If
Else
    PayFrequency = "Semi-monthly" 'other companies will have semi

End If

'set pay frequency for channels in Compressors
If ThisWorkbook.Worksheets("Ctrl").OptionButton3.Value = True Then
    If (ChannelPicked = 6) Then
        PayFrequency = "Weekly"
    ElseIf (ChannelPicked = 7) Then
        PayFrequency = "Semi-monthly"
    Else
        PayFrequency = "Bi-Weekly"
    End If
Else
    

End If






'Alert for Edward and Document type is Time sheet only
If ThisWorkbook.Worksheets("Ctrl").OptionButton7.Value = True And ThisWorkbook.Worksheets("Ctrl").OptionButton1.Value = True Then

    If (ThisWorkbook.Worksheets("Ctrl").Range("G9") < 1 Or ThisWorkbook.Worksheets("Ctrl").Range("G9") = "") Then
        MsgBox "Please input Maximum Overtime (OT)", vbExclamation
        GoTo Ende
        
    End If
    
    If (ThisWorkbook.Worksheets("Ctrl").Range("G11") < 1 Or ThisWorkbook.Worksheets("Ctrl").Range("G11") = "") Then
        MsgBox "Please input Maximum Doubletime (DT)", vbExclamation
        GoTo Ende
        
    End If
    
    If PayFrequency = "Weekly" And (ThisWorkbook.Worksheets("Ctrl").Range("G13") < 1 Or ThisWorkbook.Worksheets("Ctrl").Range("G13") = "") Then
    
        MsgBox "You are selecting PG 1/2/5. Please input minimum hours weekly", vbExclamation
        GoTo Ende
        
    ElseIf PayFrequency = "Bi-Weekly" And (ThisWorkbook.Worksheets("Ctrl").Range("G15") < 1 Or ThisWorkbook.Worksheets("Ctrl").Range("G15") = "") Then
    
        MsgBox "You are selecting PG 3/4/6/7. Please input minimum hours bi-weekly", vbExclamation
        GoTo Ende
        
    End If
    
End If


'set correct amount type

If ThisWorkbook.Worksheets("Ctrl").OptionButton1.Value = True Then
    Typ = "$"
    typ2 = "H"
    
    
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
    Typ = "H"
    typ2 = "$"
    
    
ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton6.Value = True Then 'It is allowed to be $ and Hours
    Typ = ""
    typ2 = ""
    
End If

'If ThisWorkbook.Worksheets("Ctrl").OptionButton3.Value = True Then --> moved to above "Check file properties"
'    Code = 105
'End If





'check file properties like : have have Last Name and First Name check
Set wsParam = wb.Worksheets("Param")
LastRowParam = wsParam.Cells(Rows.Count, "A").End(xlUp).Row

HeaderColumn = ""
LastFirstNameCheck = ""

For c = 2 To LastRowParam
    If wsParam.Range("A" & c) = CompId Then
        Code = wsParam.Range("B" & c).Value
        'HeaderColumn = wsParam.Range("C" & c).Value
        LastFirstNameCheck = wsParam.Range("D" & c).Value
        AmountTypeCheck = wsParam.Range("E" & c).Value
        
        
    End If

Next c



'open upload file
    fileName = ActiveWorkbook.Name
         
     filetoopen = Application.GetOpenFilename _
    (Title:="Please choose a file to process:")
       If filetoopen = False Then
          LOG.sub_Log Activity1:="Choosing file canceled."
          MsgBox "No file specified.", vbExclamation, "File error!"
       Exit Sub
    Else
    End If
    wb.Sheets("Data").Activate
    
    wb.Sheets("Data").UsedRange.Clear
    Workbooks.Open fileName:=filetoopen
    Set ws = ActiveSheet
    Openfile = ActiveWorkbook.Name
    'ws.Range("A1").Select
    

    
    
'Check Header Column exist on the file
answer = MsgBox("Does the file have Column Header ?", vbYesNo)
If answer = vbYes Then
    If Trim(CStr(ws.Cells(1, 6))) = "" Then 'check if F1 has data
        MsgBox "No column header found in column F.", vbExclamation, "Column Header missing!"
        wb.Sheets("Ctrl").Activate
        Windows(Openfile).Close SaveChanges:=False
        GoTo Ende
        
    Else
        HeaderColumn = "Yes"
        
    End If
Else
    LastColumnHeader = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For b = 1 To LastColumnHeader
        If Trim(CStr(ws.Cells(1, b))) <> "" And Trim(CStr(ws.Cells(2, b))) = "" Then 'check Row1 has data and Row2 no data
            answer2 = MsgBox("It seems the file has column header, Do you want to proceed the file ?", vbYesNo)
                If answer2 = vbYes Then
                    HeaderColumn = "No"
                    Exit For
                
                Else
                    wb.Sheets("Ctrl").Activate
                    Windows(Openfile).Close SaveChanges:=False
                    GoTo Ende
                    
                End If
    
        Else
            HeaderColumn = "No"
    
        End If
    Next b
    
End If


Dim StartTime As Double
dblStartTime = VBA.Timer

    
    If HeaderColumn = "Yes" Then
      
            ws.Cells.Select
            Selection.Copy
            Windows(fileName).Activate
            ThisWorkbook.Sheets("Data").Activate
            ActiveSheet.Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
            ActiveSheet.Range("A1").Select
    
            Windows(Openfile).Application.CutCopyMode = False
            Windows(Openfile).Close SaveChanges:=False
        
         
    Else
        ' Edwards file
        
        ws.Range("A:A").Copy wsData.Range("C1")  'CompID
        ws.Range("B:B").Copy wsData.Range("B1")  'ProdID
        ws.Range("C:C").Copy wsData.Range("G1")  'Check#
        ws.Range("D:D").Copy wsData.Range("L1")   '
        ws.Range("E:E").Copy wsData.Range("H1")  'Paygroup
        ws.Range("F:F").Copy wsData.Range("F1")  'Amt Type
        ws.Range("H:H").Copy wsData.Range("D1")  'E/D-CODE
        ws.Range("I:I").Copy wsData.Range("E1")  'AMOUNT
        ws.Range("O:O").Copy wsData.Range("M1")
         
        Windows(Openfile).Application.CutCopyMode = False
        Windows(Openfile).Close SaveChanges:=False
        

    
    End If
    
    

Call UnlockResultCells

'clear sheets
Worksheets("Calc").Cells.Clear
Worksheets("Result").Cells.Clear


If LastFirstNameCheck = "Yes" Then

    'check column "N" for LastName and column "O" for FirstName
    LastColumn = wb.Worksheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = wb.Worksheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
    
    For c = 1 To LastColumn
        If InStr(UCase(CStr(ThisWorkbook.Worksheets("Data").Cells(1, c))), UCase("Last Name")) <> 0 Then
    
            wb.Worksheets("Data").Range(Cells(1, c), Cells(LastRow, c)).Cut wb.Worksheets("Data").Range("N:N")
    
        End If
    Next c
    
    For d = 1 To LastColumn
        If InStr(UCase(CStr(ThisWorkbook.Worksheets("Data").Cells(1, d))), UCase("First Name")) <> 0 Then
    
             wb.Worksheets("Data").Range(Cells(1, d), Cells(LastRow, d)).Cut wb.Worksheets("Data").Range("O:O")
    
        End If
    Next d


    ' to get vlookup LastName and FirstName match on the employee sheet with the upload file
    
    If wb.Worksheets("Employees").AutoFilterMode Then
          wb.Worksheets("Employees").AutoFilterMode = False
     
    End If
    
    
End If

Call UnlockEmployeeMasterSheet


LastRowEmployees = wb.Worksheets("Employees").Cells(Rows.Count, "A").End(xlUp).Row

ThisWorkbook.Worksheets("Employees").Range("S2") = "=CONCATENATE(TRIM(E2),TRIM(D2))"
ThisWorkbook.Worksheets("Employees").Range("T2") = "=A2"
ThisWorkbook.Worksheets("Employees").Range("U2") = "=TEXT(C2," & Chr(34) & "00000000" & Chr(34) & ")"   'EmpID IN TEXT FORMAT(8chars)
ThisWorkbook.Worksheets("Employees").Range("V2") = "=CONCATENATE(TRIM(J2),TRIM(U2))"   'EmpID & COMPID

ThisWorkbook.Worksheets("Employees").Range("K2:W2").Copy ThisWorkbook.Worksheets("Employees").Range("K3:W" & LastRowEmployees)

Application.CutCopyMode = False



If HeaderColumn = "No" Then
    wsData.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      
End If

'change headers
  wsData.Range("A1") = "COMPID"
  wsData.Range("B1") = "PRO ID"
  wsData.Range("C1") = "CODE TYPE"
  wsData.Range("D1") = "E/D-CODE"
  wsData.Range("E1") = "AMOUNT"
  wsData.Range("F1") = "AMT TYPE"
  wsData.Range("G1") = "CHECK #"
  wsData.Range("H1") = "PAY GROUP"
  wsData.Range("N1") = "LAST NAME"
  wsData.Range("O1") = "FIRST NAME"
  
    'add headers for lookup
  If wsData.Range("I1") = "" Then wsData.Range("I1") = "*"
  If wsData.Range("J1") = "" Then wsData.Range("J1") = "*"
  If wsData.Range("K1") = "" Then wsData.Range("K1") = "*"
  If wsData.Range("L1") = "" Then wsData.Range("L1") = "*"
  If wsData.Range("M1") = "" Then wsData.Range("M1") = "*"
  
  If wsData.Range("P1") = "" Then wsData.Range("P1") = "*"
  If wsData.Range("Q1") = "" Then wsData.Range("Q1") = "*"
  If wsData.Range("R1") = "" Then wsData.Range("R1") = "*"
  If wsData.Range("U1") = "" Then wsData.Range("U1") = "*"
  If wsData.Range("X1") = "" Then wsData.Range("X1") = "*"
  If wsData.Range("Y1") = "" Then wsData.Range("Y1") = "*"
  If wsData.Range("AA1") = "" Then wsData.Range("AA1") = "*"
  If wsData.Range("AB1") = "" Then wsData.Range("AB1") = "*"
  If wsData.Range("AC1") = "" Then wsData.Range("AC1") = "*"
  If wsData.Range("AD1") = "" Then wsData.Range("AD1") = "*"
  If wsData.Range("AE1") = "" Then wsData.Range("AE1") = "*"
   
  
  wsData.Range("S1") = "VEMPID"
  wsData.Range("T1") = "VTERMDATE"
  wsData.Range("V1") = "CONNAME"
  wsData.Range("W1") = "VNAME"
  wsData.Range("Z1") = "SUBPAY"
  wsData.Range("AB1") = "VSALARYTYPE"


'LastRow = wb.Worksheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
LastRow = wb.Worksheets("Data").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

''Format B(EMPID) to number
'
'    With ThisWorkbook.Worksheets("Data").Range("B:B")
'        .NumberFormat = "General"
'        .Value = .Value
'    End With
'
''Format D(E/D-CODE) to number
'
'    With ThisWorkbook.Worksheets("Data").Range("D:D")
'        .NumberFormat = "General"
'        .Value = .Value
'    End With


    
    ' data size has less then 200 rows
    With ThisWorkbook.Worksheets("Data").Cells(1).CurrentRegion
'        With .Columns(2)
'              .NumberFormat = "General"
'              .Value = .Value
'        End With
        With .Columns(4)
            .NumberFormat = "General"
            .Value = .Value
        End With
        With .Columns(5)
            .NumberFormat = "General"
            .Value = .Value
        End With

    End With


'Vlookup data

ThisWorkbook.Worksheets("Data").Range("AD2") = EmployeeMasterCOMPID
ThisWorkbook.Worksheets("Data").Range("R2") = "=CONCATENATE(TRIM(AD2),TEXT(B2,""00000000""))"   'EmpID & COMPID

ThisWorkbook.Worksheets("Data").Range("S2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),3)," & Chr(34) & Chr(34) & ")" 'EmpID
ThisWorkbook.Worksheets("Data").Range("T2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),8)," & Chr(34) & Chr(34) & ")" 'TermDate
ThisWorkbook.Worksheets("Data").Range("U2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),20)," & Chr(34) & Chr(34) & ")" 'PG
ThisWorkbook.Worksheets("Data").Range("V2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),5)," & Chr(34) & Chr(34) & ")" 'FirstName
ThisWorkbook.Worksheets("Data").Range("W2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),4)," & Chr(34) & Chr(34) & ")" 'LastName
ThisWorkbook.Worksheets("Data").Range("Z2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),7)," & Chr(34) & Chr(34) & ")" 'SubPay/OTExempt
ThisWorkbook.Worksheets("Data").Range("AB2") = "=IFERROR(INDEX(Employees!$A$2:$V$" & LastRowEmployees & ",MATCH(Data!R2,Employees!$V$2:$V$" & LastRowEmployees & ",0),6)," & Chr(34) & Chr(34) & ")" 'SalaryType

ThisWorkbook.Worksheets("Data").Range("X2") = "=N2=W2"                            'Check LastName = LastName in master employee"
ThisWorkbook.Worksheets("Data").Range("AE2") = "=O2=V2"                            'Check FirstName = FirstName in master employee"

'ThisWorkbook.Worksheets("Data").Range("V2") = "=CONCATENATE(TRIM(O2),TRIM(N2))"
ThisWorkbook.Worksheets("Data").Range("Y2") = "=TEXT(H2,""0"")=U2"                            'Check PG = PG in master employee"

ThisWorkbook.Worksheets("Data").Range("AC2") = "=IF(F2=" & Chr(34) & Chr(34) & ",FALSE,TRUE)"                         'Check PG = PG in master employee"

ThisWorkbook.Worksheets("Data").Range("R2:AE2").Copy ThisWorkbook.Worksheets("Data").Range("R3:AE" & LastRow)
Application.CutCopyMode = False


'If it’s a time file, column G (check ID) is not correct. Currently it’s taking the line item from the time clock system. It should always be #1

If ThisWorkbook.Worksheets("Ctrl").OptionButton1.Value = True Then

ThisWorkbook.Worksheets("Data").Range("G2") = 1
ThisWorkbook.Worksheets("Data").Range("G2:G2").Copy ThisWorkbook.Worksheets("Data").Range("G3:G" & LastRow)
Application.CutCopyMode = False
End If



For i = 2 To LastRow



'Check if empid is existing

If ThisWorkbook.Worksheets("Data").Range("S" & i) = "" Then
    f = f + 1
    wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") doesn't exist in ProBusiness"
    wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
End If



'Check if emp is terminated

If ThisWorkbook.Worksheets("Data").Range("T" & i) <> 0 And ThisWorkbook.Worksheets("Data").Range("T" & i) <> "" Then
    f = f + 1
    wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") is terminated"
    wb.Worksheets("Result").Range("A" & 1 + f) = "N"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)

End If


'check if empid is only numeric

If IsNumeric(ThisWorkbook.Worksheets("Data").Range("B" & i)) = False Then
    f = f + 1
    wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") doesn't only contain Numbers"
    wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    
End If


'Check if Commission file has H in AMT Type (Error if the typ is not "$") -- or Time file has $ in AMT Type (Error if the typ is not "H") -- skip for Edwards

If ThisWorkbook.Worksheets("Data").Range("F" & i) = Typ And ThisWorkbook.Worksheets("Ctrl").OptionButton6.Value = False Then

f = f + 1
    If ThisWorkbook.Worksheets("Ctrl").OptionButton1.Value = True Then
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has $ as value in AMT Type"
        
    ElseIf ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has H as value in AMT Type"
        
    End If
    wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)

End If


'check if COMPID is blank
If CompIdOnFile = "Yes" Then
    If CStr(ThisWorkbook.Worksheets("Data").Range("A" & i)) = "" Then
        f = f + 1
            wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has blank COMPID"
            wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
            wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
End If

'check if PRO ID is blank

If CStr(ThisWorkbook.Worksheets("Data").Range("B" & i)) = "" Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has blank PRO ID"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
End If


'check if PAYGROUP is blank in column H, lookup the PG and in the employee master file and add it in the column so that it will be uploaded to the teams file

If UCase(ThisWorkbook.Worksheets("Data").Range("Y" & i)) = "FALSE" Then
      wb.Worksheets("Data").Range("H" & i) = wb.Worksheets("Data").Range("U" & i).Value
          
End If

ThisWorkbook.Worksheets("Data").Range("AA" & i) = Trim(ThisWorkbook.Worksheets("Data").Range("H" & i).Value) & Trim(ThisWorkbook.Worksheets("Data").Range("B" & i).Value)


''check if Salary Type is blank in column F, lookup the Salary type in the employee master file and add it in the column so that it will be uploaded to the teams file
'
'If UCase(ThisWorkbook.Worksheets("Data").Range("AC" & i)) = "FALSE" Then
'     wb.Worksheets("Data").Range("F" & i) = wb.Worksheets("Data").Range("AB" & i).Value
'
'End If



' check if S type is other than 135, 140, or 130
If (ThisWorkbook.Worksheets("Data").Range("F" & i) = "S" And CStr(ThisWorkbook.Worksheets("Data").Range("D" & i)) <> "135" And CStr(ThisWorkbook.Worksheets("Data").Range("D" & i)) <> "140" And ThisWorkbook.Worksheets("Data").Range("D" & i) <> "130") Then
    f = f + 1
    wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has a non valid code " & ThisWorkbook.Worksheets("Data").Range("D" & i)
    wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
End If


' code 380, or 381 give an error message that says: "Please send severance agreement to payroll and remove these entries from upload file"
If CStr(ThisWorkbook.Worksheets("Data").Range("D" & i)) = "380" Or ThisWorkbook.Worksheets("Data").Range("D" & i) = 380 Or CStr(ThisWorkbook.Worksheets("Data").Range("D" & i)) = "381" Or ThisWorkbook.Worksheets("Data").Range("D" & i) = 381 Then
    f = f + 1
    wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has code 380/381. Please send severance agreement to payroll and remove these entries from upload file"
    wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
    wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
End If





'check if either commission button or adjustment button is checked

If ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Or ThisWorkbook.Worksheets("Ctrl").OptionButton6.Value = True Then
    
    
    'If it’s a commission file, the macro should check if EMP ID matches Last Name. If it doesn’t it should be a hard error
    'If LastFirstNameCheck = "Yes" Then
    
        'Check LastName
        If ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True And UCase(ThisWorkbook.Worksheets("Data").Range("X" & i).Value) = "FALSE" And ThisWorkbook.Worksheets("Data").Range("S" & i) <> "" Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") and code: " & ThisWorkbook.Worksheets("Data").Range("D" & i) & " doesn't match Last Name:" & ThisWorkbook.Worksheets("Data").Range("W" & i)
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
               
        End If
        
        'Check FirstName
        If ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True And UCase(ThisWorkbook.Worksheets("Data").Range("AE" & i).Value) = "FALSE" And ThisWorkbook.Worksheets("Data").Range("S" & i) <> "" Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") and code: " & ThisWorkbook.Worksheets("Data").Range("D" & i) & " doesn't match First Name:" & ThisWorkbook.Worksheets("Data").Range("V" & i)
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
               
        End If
    'End If
        
    'check if the commission amount is below 0 for a term employee, error "EmployeeID has commission amount below 0 for a term employee. Please remove these entries from upload file"

    If (ThisWorkbook.Worksheets("Data").Range("T" & i) <> 0 And ThisWorkbook.Worksheets("Data").Range("T" & i) <> "") And (ThisWorkbook.Worksheets("Data").Range("E" & i) < 0 And CStr(ThisWorkbook.Worksheets("Data").Range("E" & i)) <> "") Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has Commission amount below 0 for a term employee. Please remove these entries from upload file"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
    
    
    'check if amount under 0, Check# should be 1 (error if it is not 1)
    If (ThisWorkbook.Worksheets("Data").Range("E" & i) <= 0 And CStr(ThisWorkbook.Worksheets("Data").Range("E" & i)) <> "") And ThisWorkbook.Worksheets("Data").Range("G" & i) <> 1 And ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has Amount less than 0 and check# is not 1"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
    
    'check if amount above 0, Check# should not be 1 (error if it is 1)
    If (ThisWorkbook.Worksheets("Data").Range("E" & i) > 0 And CStr(ThisWorkbook.Worksheets("Data").Range("E" & i)) <> "") And ThisWorkbook.Worksheets("Data").Range("G" & i) = 1 And ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has Amount above than 0 and check# is 1"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
    
     'check if amount commission is blank
    If CStr(ThisWorkbook.Worksheets("Data").Range("E" & i)) = "" Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has blank Amount"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
    
    ' Error if the code is not 300
    If (CStr(ThisWorkbook.Worksheets("Data").Range("D" & i)) <> "300" Or ThisWorkbook.Worksheets("Data").Range("D" & i) <> 300) And ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has code not 300"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
    
     ' Error if the typ is not "$" -- rule for commission file (not for adjustment file)
    If CStr(ThisWorkbook.Worksheets("Data").Range("F" & i)) <> "$" And ThisWorkbook.Worksheets("Ctrl").OptionButton2.Value = True Then
        f = f + 1
        wb.Worksheets("Result").Range("B" & 1 + f) = "EmpID " & ThisWorkbook.Worksheets("Data").Range("B" & i) & "(" & ThisWorkbook.Worksheets("Data").Range("O" & i) & " " & ThisWorkbook.Worksheets("Data").Range("N" & i) & ") has H as value in AMT Type"
        wb.Worksheets("Result").Range("A" & 1 + f) = "Y"
        wb.Worksheets("Result").Range("G" & 1 + f) = ThisWorkbook.Worksheets("Data").Range("B" & i)
    End If
   

End If


    
Next i
…
vbaProject_00.bin vba-project OOXML VBA project: xl/vbaProject.bin 394752 bytes
SHA-256: 1c3700d41e83b316148efe2e7f446a8ca2b65963013b1095ebfd9bd8668d64bb
emf_00.emf ooxml-emf OOXML EMF part: xl/media/image2.emf 3116 bytes
SHA-256: a7b6bd176b3d362446c21fea4fdd6f29be3ad87b5be7fa103a065a882aae34e6
emf_01.emf ooxml-emf OOXML EMF part: xl/media/image11.emf 3116 bytes
SHA-256: f019f8094a6d8690f6faa4b131d3346a98095da27e66b12f911afef070ee8f6c
emf_02.emf ooxml-emf OOXML EMF part: xl/media/image9.emf 3116 bytes
SHA-256: 81b43f0b9027d18fa35576e9912ff5fafb98ede65447b228877dc7ac87919b01
emf_03.emf ooxml-emf OOXML EMF part: xl/media/image3.emf 2980 bytes
SHA-256: 862b83017dbe75cd7ec4a83b9451f66eb584a785f186d4030194219477c78879
emf_04.emf ooxml-emf OOXML EMF part: xl/media/image6.emf 3068 bytes
SHA-256: 6f5d862edcbd9fa42c011a314817731a2120496b38506d6fde645aafd6b80a3f
emf_05.emf ooxml-emf OOXML EMF part: xl/media/image8.emf 3116 bytes
SHA-256: 7ba9f1014c1fe939bf07f52b57dda8807256fb16848853adf1b378e365b72e28
emf_06.emf ooxml-emf OOXML EMF part: xl/media/image4.emf 3032 bytes
SHA-256: bc1985b2692365d856437813d1b93d81d9c5467944a37758f1cf48b2c2f88d1b
emf_07.emf ooxml-emf OOXML EMF part: xl/media/image10.emf 3116 bytes
SHA-256: 3c512d5b8259e1f6cb0e39412b6fca875f32eb716b261f40f541def33fdfc860
emf_08.emf ooxml-emf OOXML EMF part: xl/media/image1.emf 3148 bytes
SHA-256: 7b85ef09d1c4e8868ac28271dede9961b53b0640974e62519c90d00ff330a491
emf_09.emf ooxml-emf OOXML EMF part: xl/media/image7.emf 3116 bytes
SHA-256: 010f9603a7e2d27afbff35f44a66a47762cb4e3b37a327055e350c3c985da60d
emf_10.emf ooxml-emf OOXML EMF part: xl/media/image5.emf 3172 bytes
SHA-256: 7846c78148ec7ce1a42f52bd5840625d99cf9c4940c3284ed3aaad4679a0ae42
emf_11.emf ooxml-emf OOXML EMF part: xl/media/image12.emf 3116 bytes
SHA-256: bc520b5738232e0b9e5d04fc270936afd777c8984284bc296f4bfada77737ad0
emf_12.emf ooxml-emf OOXML EMF part: xl/media/image13.emf 3172 bytes
SHA-256: 04e109bc2b63c5ab715e3543f91698c1513efb19c801f3253bccc53dc241b1f4

Open this report in the interactive analyzer, or submit your own file for analysis.