Check cell contents are correct/as expected

[SueBK]

I have a set of worksheets with data broken into states. Each state is 'allowed' to add columns to the end of my dataset but they are not allowed to change my column structure in anyway (add, delete, rearrange). They are also not supposed to add new rows without me giving them an ID number for column A.

Every morning I need to merge these worksheets into a single sheet. This is just one question that I have for the process that I've devised. (I figure breaking them down into separate posts would be easier to answer.)

Step 2 of my process is to check that users have not tampered with my column headings in any way. How would I write procedure that check each cell in a range A1 to CT1 against a list. That is A1 must equal "ID", B1 must equal "Site Name", else give me a message box telling me which heading is wrong and end the macro.

 

[dmt32]

Hi,
You can create a function to check for valid headers in range against a defined list
- not fully tested but maybe something like following will give you ideas to work with:

Place in standard module:

Function IsValid(ByVal sh As Object) As Boolean
    Dim i As Long
    Dim ValidHeaders As Variant, arr As Variant
    
    'List All Valid Header Names In correct Order Here
    ValidHeaders = Array("ID", "Site Name", "Another Name Here")
    
    'create array of the sheets Headers in range
    arr = Application.Transpose(sh.Range("A1:CT1").Value)


    On Error Resume Next
    For i = LBound(arr) To UBound(arr)
        '1st check heading exists in the valid list
        If Application.Match(arr(i, 1), ValidHeaders, False) = 0 Then
            MsgBox arr(i, 1) & Chr(10) & "Heading Is Not Valid", 16, "Error"
            Exit Function
        Else
            'then check heading in correct order
            If arr(i, 1) <> ValidHeaders(i) Then
                MsgBox arr(i, 1) & Chr(10) & ValidHeaders(i) & Chr(10) & Chr(10) & _
                       "Headers Are Not In Correct Order", 16, "Error"
                Exit Function
            End If
        End If
    Next i
    IsValid = True
End Function

Enter your header names in the Array where shown and then call function within you code like this:

Option Base 1
Sub atest()
    Dim ws As Worksheet


    'the sheet you want to check
    Set ws = Worksheets("Sheet1")


    'call the function to check if
    'sheet headers are valid
    If IsValid(ws) Then
        ' do valid stuff here
    Else
        'and not valid stuff here
    End If
End Sub

Set the ws object variable to the sheet you want to check.
Note: Option Base 1 must be placed at top of your module.

Function should first check that header name exists in your valid list & then if the header is in the correct order.

Hope Helpful

Dave

 

[SueBK]

Thank you. I decided I was over-thinking it. If the headers are incorrect, then they'll need to fixed manually ('cause who knows what the user has done with them).
I've created a hidden worksheet with my set headings and a simple "Sheet1!A1 = A1" formula for each of the state worksheets. A countif (False) for the whole range in another cell, and big "if Countif >0 "FIX THE HEADINGS" in another.
In my sheet-merge macro, I've simply put a messagebox "Have you checked the headings are correct? If not, cancel and do it now."

 

[SueBK]

Got cleverer. Changed my message to read the countif false. If it's greater than zero, it says "You have an issue" and cancels the macro. The headings have to be fixed manually before proceeding so this makes sense. If the count of falses is 0, it continues on with the macro.