Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Headers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Headers
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).Of fset(, 1) = cell.Offset(, -1).Value End If Next End Sub "Dow" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Headers
On May 20, 9:05*am, "Patrick Molloy"
wrote: 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).Of fset(, 1) = cell.Offset(, -1).Value * * * * * * End If * * * * *Next End Sub "Dow" wrote in message ... 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- Hide quoted text - - Show quoted text - Patrick, Thank you. This made perfect sense and it works like a charm. Dow. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Columns based on missing headers | Excel Programming | |||
lookup using column headers and row headers | Excel Discussion (Misc queries) | |||
Missing Column Headers / Can't scroll to top | Excel Discussion (Misc queries) | |||
Hide Column Headers but not row (Headers) | Excel Programming | |||
Missing Column and Row Headers? | Excel Discussion (Misc queries) |