Maco question

  • Thread starter Thread starter Pawan
  • Start date Start date
P

Pawan

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
 
Thanks OssieMac,

But then I need to write this for all 50 headers. Is there any other short
way to do this?
 
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.
 
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. :)
 
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
 
This is great...
Its perfect. However, it gives message one by one for all unavailable
headers. I wanted to see all unavailable headers in one mesagebox. Is it
possible?

Thank you once again. :)
 
My apologies for not getting back to you sooner but the Microsoft site is not
sending email notifications and I missed checking this one earlier.

Anyway try the following. Should do what you want.

If you don't want the message to tell you "No missing headers" then delete
the line with Else and the MsgBox line following Else (I have a comment
relating to this at the point in the code).

Sub ValidateHeaders()

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

'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")
strMsge = "" 'Initialize message string
'Loop through the valid headers
For Each cel In rngHeaders
cl_City = Application.Match(cel.Value, .Rows(1), 0)
If IsError(cl_City) Then
strMsge = strMsge & cel.Value & vbCrLf
End If
Next cel
End With

If Len(strMsge) > 0 Then
strMsge = "Following headers do not exist:" _
& vbCrLf & strMsge

'Following 2 lines optional.
Else
strMsge = "No missing headers"

End If

MsgBox strMsge

End Sub
 
Back
Top