Hello

I have a worksheet with several columns. In my macro, I have written code to
identify column number for each heading. e.g. If there is a column with
heading "City", then I have written cl_city=Application.Match("City",
Rows(1),0).
There are several such columns. However, I want to notify user is any of the
column is not existing. So if "City" column is not existing, then macro
should throw a message to user.
How can I do this. I have around 50 columns.

Any help is highly appreciated.

Thank You

Regards,
prm

Dim cl_City

cl_City = Application.Match("City", Rows(1), 0)
If IsError(cl_City) Then
MsgBox "City does not exist as a header"
End If

--
Regards,

OssieMac


"Pawan" wrote:

Hello

I have a worksheet with several columns. In my macro, I have written code to
identify column number for each heading. e.g. If there is a column with
heading "City", then I have written cl_city=Application.Match("City",
Rows(1),0).
There are several such columns. However, I want to notify user is any of the
column is not existing. So if "City" column is not existing, then macro
should throw a message to user.
How can I do this. I have around 50 columns.

Any help is highly appreciated.

Thank You

Regards,
prm

Thanks OssieMac,

But then I need to write this for all 50 headers. Is there any other short
way to do this?


"OssieMac" wrote:


Dim cl_City

cl_City = Application.Match("City", Rows(1), 0)
If IsError(cl_City) Then
MsgBox "City does not exist as a header"
End If

--
Regards,

OssieMac


"Pawan" wrote:

Hello

I have a worksheet with several columns. In my macro, I have written code to
identify column number for each heading. e.g. If there is a column with
heading "City", then I have written cl_city=Application.Match("City",
Rows(1),0).
There are several such columns. However, I want to notify user is any of the
column is not existing. So if "City" column is not existing, then macro
should throw a message to user.
How can I do this. I have around 50 columns.

Any help is highly appreciated.

Thank You

Regards,
prm

Somewhere you will need a list of the 50 headers. Is this code in a separate
workbook to the ones you are testing? If so, you could have the list on a
worksheet within the workbook containing the code and I can then give you
code to loop through the list and test for all.

Can you give me a worksheet name and range where the list can be permanently
stored.

--
Regards,

OssieMac

The code is in the same workbook. Few of the headers are "City", "Address",
"Code1", "Code2", "Post". If you can give me code for these, then I will add
remaining headers in it.

Thank you once again. :)

"OssieMac" wrote:

Somewhere you will need a list of the 50 headers. Is this code in a separate
workbook to the ones you are testing? If so, you could have the list on a
worksheet within the workbook containing the code and I can then give you
code to loop through the list and test for all.

Can you give me a worksheet name and range where the list can be permanently
stored.

--
Regards,

OssieMac

Try the following.

Create or re-name a worksheet in the workbook and call it "Headers".

In cell A1 of the Headers worksheet insert "Valid Headers" (or any name you
like as a column header for the validation list).

In cell A2, A3, A4 etc enter the names of your headers. Note you are going
down the column; not across the worksheet. You can have as many or few as you
like and can add and remove them later because the code tests for the last
cell containing data.

You may choose to hide the headers worksheet if you like.

Copy the following code into the VBA editor. Note the comments.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.


Sub ValidateHeaders()

Dim wsHeaders As Worksheet
Dim rngHeaders As Range
Dim cel As Range
Dim cl_City As Variant 'Must be Variant

'Assign headers worksheet to a variable
Set wsHeaders = ThisWorkbook.Sheets("Headers")

With wsHeaders
'Test to ensure that at least one entry
'in the valid headers list
If IsEmpty(.Range("A2")) Then
MsgBox "No headers entered in validation list"
Exit Sub
End If

'Assign range of valid headers to a variable
Set rngHeaders = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

'Edit "Sheet1" to match the sheet you are testing
With Sheets("Sheet1")
'Loop through the valid headers
For Each cel In rngHeaders
cl_City = Application.Match(cel.Value, .Rows(1), 0)
If IsError(cl_City) Then
MsgBox cel.Value & " does not exist as a header"
'Exit Sub 'If you want to exit and insert header
End If
Next cel
End With

End Sub


--
Regards,

OssieMac