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

mayaa_mmm

Board Regular
Joined
Jul 30, 2014
Messages
54
Office Version
  1. 2010
Platform
  1. Windows
MarkClassGradeStatus
80UGAPASS
Suddenly in my report , new column header got added as below
MarkClassGradeStatusNameCountry
80UGAPASSJohnIndia

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"
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
VBA Code:
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.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top