Greek live music concerts and dance evenings
with the experience of 23 years and 1500 events
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