CODEWITHSUNDEEP
vbaexcel
Gyana Prakash
Gyana Prakash

Reputation: 79

VBA to check the specific columns are exists in the sheet

Before running into main procedure here i am checking whether some specific columns are exists in the sheet1 or not, But the problem is in that raw data sheet columns like "Employee Name" & "Designation" are in that sheet, but when run the below code it is giving an message "Not Found" and it is coming out of that loop, Can you please help me on this issue.

Dim rngX As Range
Sub Colvalidation1()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Work Geography", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation2
Else
    MsgBox "Work Geography - Column Not Found"
End If
End Sub

Sub Colvalidation2()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Work Country",
lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation3
Else
    MsgBox "Work Country - Column Not Found"
End If
End Sub

Sub Colvalidation3()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Project #", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation4
Else
    MsgBox "Project # - Column Not Found"
End If
End Sub

Sub Colvalidation4()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Project Name", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation5
Else
    MsgBox "Project Name - Column Not Found"
End If
End Sub

Sub Colvalidation5()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Employee #", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation8
Else
    MsgBox "Employee # - Column Not Found"
End If
End Sub

Sub Colvalidation6()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Employee Name", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation7
Else
    MsgBox "Employee Name - Column Not Found"
End If
End Sub

Sub Colvalidation7()

Set rngX = Worksheets("Sheet1").Range("A1:S1").Find("Designation", lookat:=xlPart)
If Not rngX Is Nothing Then
Call Colvalidation8
Else
    MsgBox "Designation - Column Not Found"
End If
End Sub

Upvotes: 0

Views: 10913

Answers (2)

DisplayName
DisplayName

Reputation: 13386

you don't need to duplicate code

use an Array to hold your column header to search for and one helper Sub or Function to call for each of them

if you want to stop at the first found column header, the following would do:

Option Explicit

Dim rngX As Range
Sub main()
    Dim vals As Variant, val As Variant
    vals = Array("Work Geography", "Work Country", "Project #", "Project Name", "Employee #", "Employee Name")

    Dim rngToSearchIn As Range
    Set rngToSearchIn = Worksheets("Sheet1").Range("A1:S1")

    For Each val In vals
        If Colvalidation(rngToSearchIn, val) Then Exit For
    Next
    If Not rngX Is Nothing Then MsgBox "found " & val & " in " & rngX.Address
End Sub

Function Colvalidation(rng As Range, value As Variant) As Boolean
    Set rngX = rng.Find(what:=value, lookat:=xlPart, LookIn:=xlValues)
    Colvalidation = Not rngX Is Nothing
    If Not Colvalidation Then MsgBox value & " - Column Not Found"
End Function

While if you want to process all column headers, then the following would do:

Option Explicit

Dim rngX As Range

Sub main()
    Dim vals As Variant, val As Variant
    vals = Array("Work Geography", "Work Country", "Project #", "Project Name", "Employee #", "Employee Name")

    Dim rngToSearchIn As Range
    Set rngToSearchIn = Worksheets("Sheet1").Range("A1:S1")

    For Each val In vals
        Colvalidation rngToSearchIn, val
    Next
End Sub

Sub Colvalidation(rng As Range, value As Variant)
    Set rngX = rng.Find(what:=value, lookat:=xlPart, LookIn:=xlValues)
    If rngX Is Nothing Then
        MsgBox value & " - Column Not Found"
    Else
        MsgBox "found " & value & " in " & rngX.Address
    End If
End Sub

Upvotes: 2

Maddy
Maddy

Reputation: 781

Please try this

    Sub Find_Columns()

    Dim rngToSearch As Range
    Dim WhatToFind As Variant
    Dim iCtr As Long

    Set rngToSearch = ThisWorkbook.Worksheets("Sheet1").Range("A1:S1")

    WhatToFind = Array("Project Name", "Employee #", "Employee Name") 'add all Column header that you want to check

    With rngToSearch
        For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
            If WorksheetFunction.CountIf(rngToSearch, WhatToFind(iCtr)) > 0 Then ' Check if column is preset or not
                    MsgBox WhatToFind(iCtr) & " Column Found" ' Pop-up msg if column is exist
            Else
                    MsgBox WhatToFind(iCtr) & " Column Not Found" ' Pop-up msg if column is Not Found
            End If
        Next
    End With

    End Sub

Upvotes: 4

Related Questions