top of page

Sub AddMacroComments()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim data As Variant
    Dim output() As Variant
    Dim i As Long
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change if needed

    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Find column numbers dynamically by header names
    Dim headers As Range
    Set headers = ws.Rows(1)
    
    Dim colActiveStatus As Long, colAlertName As Long
    Dim colDivision As Long, colWorkerSubType As Long
    Dim colAccrualMethod As Long, colComment As Long
    
    colActiveStatus = Application.Match("ActiveStatus", headers, 0)
    colAlertName = Application.Match("AlertName", headers, 0)
    colDivision = Application.Match("Division", headers, 0)
    colWorkerSubType = Application.Match("WorkerSubType", headers, 0)
    colAccrualMethod = Application.Match("Accrual Method", headers, 0)
    colComment = Application.Match("Macro comment", headers, 0)
    
    If IsError(colActiveStatus) Or IsError(colAlertName) Or IsError(colDivision) Or _
       IsError(colWorkerSubType) Or IsError(colAccrualMethod) Or IsError(colComment) Then
        MsgBox "One or more required columns not found. Please check headers.", vbExclamation
        Exit Sub
    End If

    ' Load data into array for speed
    Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column))
    data = dataRange.Value
    ReDim output(1 To UBound(data), 1 To 1) ' Only one column for Macro comment

    ' Process each row
    For i = 1 To UBound(data)
        Dim comment As String
        comment = ""
        
        If Trim(data(i, colActiveStatus)) = "Terminated" Then
            comment = "Close - Terminated"
        ElseIf Trim(data(i, colAlertName)) = "Business Title change" And _
               Trim(data(i, colDivision)) = "Parametric" Then
            comment = "Keep open - imbac to handle"
        ElseIf Trim(data(i, colWorkerSubType)) = "Intern" And _
               Trim(data(i, colAccrualMethod)) = "No Decision" Then
            comment = "Close - Intern, No decision"
        End If
        
        output(i, 1) = comment
    Next i

    ' Write results back to "Macro comment" column
    ws.Range(ws.Cells(2, colComment), ws.Cells(lastRow, colComment)).Value = output

    MsgBox "Macro comments updated for " & UBound(data) & " rows.", vbInformation
End Sub

 

bottom of page