find the new column header based on the stored list in VBA

[mayaa_mmm]

Mark	Class	Grade	Status
80	UG	A	PASS

Suddenly in my report , new column header got added as below

Mark	Class	Grade	Status	Name	Country
80	UG	A	PASS	John	India

In VBA,
1. I need a logic to catch this new column and shown as message
2. Name and Country newly added to the report
3. Pop up message should show saying " Name and Country headers are added to the report"

 

[Joe4]

Here is VBA code that will compare the new headers against what is stored in the VBA code (in the array), and spit out any differences.

Sub MyHeaderCheck()

    Dim hdrs()
    Dim i As Long
    Dim c As Long
    Dim str As String
    
'   Set headers values to store in array
    hdrs = Array("Mark", "Class", "Grade", "Status")
    
'   Find last column with entry in header row (row 1)
    c = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all headers on sheet
    For i = 1 To c
'       See if header is found in arrary
        If Not IsNumeric(Application.Match(Cells(1, i), hdrs(), 0)) Then
'           If not, add to our string
            str = str & Cells(1, i) & ","
        End If
    Next i
    
'   See if any unmatched values
    If Len(str) > 0 Then
        MsgBox "The following are new headers:" & vbCrLf & Left(str, Len(str) - 1), vbOKOnly
    Else
        MsgBox "No new headers!", vbOKOnly
    End If

End Sub

Hopefully, you can adapt this to your needs.

 

[mayaa_mmm]

Here is VBA code that will compare the new headers against what is stored in the VBA code (in the array), and spit out any differences.

Sub MyHeaderCheck()

    Dim hdrs()
    Dim i As Long
    Dim c As Long
    Dim str As String
  
'   Set headers values to store in array
    hdrs = Array("Mark", "Class", "Grade", "Status")
  
'   Find last column with entry in header row (row 1)
    c = Cells(1, Columns.Count).End(xlToLeft).Column
  
'   Loop through all headers on sheet
    For i = 1 To c
'       See if header is found in arrary
        If Not IsNumeric(Application.Match(Cells(1, i), hdrs(), 0)) Then
'           If not, add to our string
            str = str & Cells(1, i) & ","
        End If
    Next i
  
'   See if any unmatched values
    If Len(str) > 0 Then
        MsgBox "The following are new headers:" & vbCrLf & Left(str, Len(str) - 1), vbOKOnly
    Else
        MsgBox "No new headers!", vbOKOnly
    End If

End Sub

Hopefully, you can adapt this to your needs.

Perfectly worked for my coding. It is very much useful for large report cleanup.
Thank you for your great help

 

[mayaa_mmm]

I need one more enhancement.
In my case, some of my column headers have to be fixed value and ignore when logic runs

Mark,Class should be ignore list of array
and logic should find Name and Country.

Is it possible?

 

[Joe4]

I need one more enhancement.
In my case, some of my column headers have to be fixed value and ignore when logic runs

Mark,Class should be ignore list of array
and logic should find Name and Country.

Is it possible?

I don't understand what you are asking.
Can you give an example?