vba validate - headers in input sheet

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I want to check whether all headers are present in Input sheet.
I am using below code, is there any other way. Thanks in advance!


My attempted code

VBA Code:
Sub Header_Validation()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets(1)
    
    Dim headers As String
    headers = "Sales,Total,Region,SalesPerson"

    If Header_Check(sh, headers) = False Then
    
        MsgBox "Header not found" & Chr(10) & headers, 16, "Header Missing in " & sh.Name
     
    End If


End Sub


Function Header_Check(ByVal sh As Worksheet, headers As String, Optional hrow As Long = 1) As Boolean

    Dim Header_Array() As String
    Dim i As Integer
    Dim cnt As Integer
        
    Header_Array = Split(headers, ",")
    headers = ""
    cnt = 0
    
    For i = 0 To UBound(Header_Array)
            
            cnt = 0
            
            On Error Resume Next
               cnt = Application.WorksheetFunction.Match(Header_Array(i), sh.Rows(hrow), 0)
            On Error GoTo 0
            
            If cnt = 0 Then
                If headers = "" Then
                    headers = Header_Array(i)
                Else
                    headers = Header_Array(i) & "," & Header_Array(i)
                End If
            End If
    Next i


Header_Check = CBool(Len(headers) = 0)
 
End Function
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
Hi,
looks like you are trying to adapt last code you posted

try this change to your codes

VBA Code:
Sub Header_Validation()
    Dim headers As String
    Dim sh As Worksheet
   
    Set sh = ThisWorkbook.Worksheets(1)
   
    headers = "Sales,Total,Region,SalesPerson"

    If Not Header_Check(sh, headers) Then
   
        MsgBox "Header not found" & Chr(10) & headers, 16, "Header Missing in " & sh.Name
    
    End If


End Sub



Function Header_Check(ByVal sh As Object, ByRef headers As String, Optional ByVal hrow As Long = 1) As Boolean
    Dim headerarray() As String
    Dim i As Integer
    Dim m as Variant
   
    headerarray = Split(headers, ",")
    headers = ""
    For i = 0 To UBound(headerarray)
        m = Application.Match(headerarray(i), sh.Rows(hrow), 0)
        If IsError(m) Then headers = headers & Chr(10) & headerarray(i)
    Next i
       
    Header_Check = CBool(Len(headers) = 0)
       
End Function

Dave
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Dmt32,

Thanks once again, liked your coding .

Function Header_Check(ByVal sh As Object, ByRef headers As String, Optional ByVal hrow As Long = 1) As Boolean


One question :=> ByRef headers As String ... do we need to provide Byref as arg or byval.


Thanks
mg
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
You pass arguments to a procedure (sub or function) by reference or by value. By default, Excel VBA passes arguments by reference. In this case, it is not necessary to declare a parameter ByRef I just choose to do so to make it clear this is what I intended.

passing arguments by reference (ByRef) the code is referencing the original value so any changes made to the variable in the called procedure are passed back to the calling procedure.

passing arguments by value (ByVal) is just passing a copy of the data i.e. the underlying element is non modifiable and any changes made will not be reflected in the calling procedure.

How you pass arguments depends if there is a need to protect the variable from being changed (ByVal) or if you need to return any modifications to it back to the calling procedure (ByRef)

There is plenty of reading material online if require further information.

Dave
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Dave,

Thanks once again for help. (y) 🕺


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,130,176
Messages
5,640,614
Members
417,158
Latest member
jimmy1986

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
Top