How to check row header on the sheet

[Gwhaou]

Hello currently I'm trying to export data from a workbook without open it by using macro, to explore the limits of VBA I'm trying to check if some specific headers are present on the sheet.

Exemple code :

Sub Checking()
  
  Dim Cell_Date As Range
  Set Cell_Date = Range("A5:CJ5").Find("DATE")
  
  If Cell_Date Is Nothing Then
    MsgBox "The header Is not their"
  Exit Sub
  
  End If
  
  Range(Cell_Date, Cell_Date.End(xlDown)).Select

End Sub

The code works perfectly but in my actual sheet I have two header which are "DATE" and "DATE linked" both start with DATE, the problem is that the code select "DATE linked" column instead of "DATE".
I need some help to solve this problem.

In the same way I have a request, I is possible to check a list of Headers directly in on sub without creating specific subs, like for headers ("DATE", "TIME", "ID", "NAME") so I can inform the user If all the data are on the sheet or not.


I hope you understand my problem.

 

[Anthony47]

Try using

Set Cell_Date = Range("A5:CJ5").Find("DATE", lookat:=xlWhole)

 

[Gwhaou]

Try using

Set Cell_Date = Range("A5:CJ5").Find("DATE", lookat:=xlWhole)

Works perfectly !

At the same time is there a way to check a bunch of header without creating seperate subs for each header ?

 

[Anthony47]

At the same time is there a way to check a bunch of header without creating seperate subs for each header ?

It depends on what you need to check...

For example, this will tell you if one header (the one in "A5", in the example) is in a list:

Dim myMatch As Variant

myarr = Array("One", "Two", "Three", "Four")

myMatch = Application.Match(Range("A5").Value, myarr, False)

If IsError(myMatch) Then
    'What to do if Range("A5").Value is not in myArr
Else
    'myMatch contains 1-2-3... depending on the position of the match in myArr
End If

This will check that a range contains a list of values:

myarr = Array("One", "Two", "Three", "Four")
If Application.WorksheetFunction.TextJoin("-", True, Range("A5:D5")) = _
  Application.WorksheetFunction.TextJoin("-", True, myarr) Then
     
    'What to do if the two arrays ARE equal
    
Else
    
        'What to do if the two arrays ARE NOT equal

End If

(this probably will not work in Office 2010)

This will count how many elements are in common between a range and a list:

Dim InCommon as Long

myarr = Array("One", "Two", "Three", "Four", "Five", "Six")

InCommon = Evaluate("SUM(COUNTIF(A5:D5," & "{""" & Application.WorksheetFunction.TextJoin(""",""", False, myarr) & """}" & "))")

(again, probably not Ok in 2010)

I bet you have in mind a different case, isn't it?

 

[Gwhaou]

For example let's take this table. My first code can check only DATE one time.

But I want to check if the headers "DATE" "ID" "AGE", are actually on the Sheet1 from the first row.

So at the end, let say if the sheet doesn't contain AGE but have the two other DATE and ID, it says that "Sorry All the information needed are not on the sheet"
Because I have a another macro which needs all the 3 information to be run.

I think I'm asking a complicate task

 

[Anthony47]

You should consider the third example, that "will count how many elements are in common between a range and a list"

Use myarr = Array("DATE", "ID", "AGE ") and Range("A1:E1")

 

[Gwhaou]

Super I gonna try that

Unfortunaly I'm on 2010

 

[Anthony47]

In Office 2010 you don't have the function TextJoin; therefore you need to insert the "list" (to be matched against the range) in a different format:

myList = "{""One"",""Two"",""Three"",""Four"",""Five""}"
InCommon = Evaluate("SUM(COUNTIF(A5:D5," & myList & "))")

This will tell you how many elements are common between myList and "A5:D5" of the active sheet

 

[Gwhaou]

Hello, yesterday I did some research and I found a code which is appropriate in my case.
I would like you to take a look

Sub ChekingHeader()

  Dim sh As Worksheet, arrH As Variant, El As Variant, C As Range
  Dim boolFound As Boolean, strNotFound As String, lastCol As Long
  
    arrH = Split("HeaderX,HeaderY,HeaderZ,Header...", ",") 'Add your Headers names 
    Set sh = ActiveSheet 'checking on the actual sheet
    lastCol = sh.Cells(1, Cells.Columns.Count).End(xlToLeft).Column 'took information from the the first row to the last column for the comparaison 

    For Each El In arrH
        boolFound = False
        For Each C In sh.Range(sh.Cells(1, 1), sh.Cells(1, lastCol)) 'check from de A1 to the last column in the first row 
            If UCase(El) = UCase(C.Value) Then 'if the values matches
                boolFound = True: Exit For
            End If
        Next
        If Not boolFound Then strNotFound = strNotFound & El & vbCrLf 'if the values don't matches
    Next
    If strNotFound <> "" Then
       MsgBox "Caution, the next headers are missing in your worksheet please check that ! " & vbCrLf & strNotFound
    Else
        MsgBox "The Headers are available"
    End If
End Sub

If there are points to correct and to improve the operation, I am a taker

And thanks again for your help.

 

[Anthony47]

My opinion is that "if that works for you then it is perfect"

Just to highlight a different tecnique:

    Dim AllIn As Long
   
    myHeads = "HeaderX,HeaderY,HeaderZ,Header..."

        AllIn = 1
        For Each C In Range(sh.Cells(1, 1), sh.Cells(1, lastCol)) 'check from de A1 to the last column in the first row
            AllIn = AllIn * InStr(1, myHeads & ",", C & ",", vbTextCompare)
            If AllIn = 0 Then Exit For
        Next C
        If AllIn = 0 Then
            'What to do if someone is missing
        Else
            'What to do if Headers are all there
        End If

There are many ways to get the job done...