jeudi 2 février 2017

If/Else Checking header order of entire row

I'm trying to run an if/else test that will look at a row of column headers and throw out an error/stop sub if the header row does not match a specific order of header names.

I have a very basic code put together for initial testing which is working, however, it is looking at each individual column header name so I'm getting a long list of message box notifications.

I know there is a way that I could just have the macro look at the entire row as a whole and either proceed or stop based on if the row matches or not. I just can't figure out how/what to manipulate in my code. Pretty new at this. Any help would be appreciated!

Sub testheaders()

Dim arrCols, x As Long, sht As Worksheet, f As Range, s

'All the fields in the final version in specific order needed
arrCols = Array("Plan Number", "Plan Name", "Division Basis    ", "Division Value    ", "Division Name    ", "SSN", "SSN Ext", "Participant Name", "Hire Date", "Term Date", "LOA Reason", ...........)

Set sht = ActiveSheet


For Each Row In arrCols
    Set f = sht.Rows(1).Find(What:=s, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        'header found
        MsgBox "header found"

    Else
        'not found
        MsgBox "missing header"

    End If

Next s

End Sub

Aucun commentaire:

Enregistrer un commentaire