Missing Headers

[Dow]

I have a list of 35 words and phrases that may appear as headers in my
spreadsheet. They do not always appear though.

I would like to write a script that will find the ones missing and add
them.

I found this, but it only works one at a time:

Dim XLCell As Range

Set XLCell = Sheets("Test").Range("1:1").Find("Acknowledge", ,
xlValues,
xlWhole)
If XLCell Is Nothing Then Sheets("Test").Range
("B1").EntireColumn.Insert
Sheets("Test").Range("B1").Value = "Acknowledge"

I thought of using an Array to help but I have not had any luck with
the script.

Does anyone out there have some suggestions?

Thank you again for all the help,

Dow. MH

 

[Patrick Molloy]

if you have your full list in a table - maybe on another sheet, you can use
MATCH in the next column to check if they're in the header row
....thats just sheet functions
so your code just needs to check this

suppose those 35 checks are in a range named 'checks' on sheet2 and your
headers are in row 1 of sheet1

your formula would be =MATCH(A1,Sheet1!1:1,false)

this will be a number for headers that match or #N/A if they're missing

then run this sub...
Sub FillMissing()
Dim cell As Range
Dim target As Range
For Each cell In Worksheets("sheet2").Range("Checks").Cells
If IsError(cell.Value) Then
Worksheets("Sheet1").Range("IV1").End(xlToLeft).Offset(, 1)
= cell.Offset(, -1).Value
End If
Next
End Sub

 

[Dow]

if you have your full list in a table - maybe on another sheet, you can use
MATCH in the next column to check if they're in the header row
...thats just sheet functions
so your code just needs to check this

suppose those 35 checks are in a range named 'checks' on sheet2 and your
headers are in row 1 of sheet1

your formula would be =MATCH(A1,Sheet1!1:1,false)

this will be a number for headers that match or #N/A if they're missing

then run this sub...
Sub FillMissing()
    Dim cell As Range
    Dim target As Range
        For Each cell In Worksheets("sheet2").Range("Checks").Cells
            If IsError(cell.Value) Then
                Worksheets("Sheet1").Range("IV1").End(xlToLeft).Offset(, 1)
= cell.Offset(, -1).Value
            End If
         Next
End Sub
















- Show quoted text -

Patrick,

Thank you. This made perfect sense and it works like a charm.

Dow.