Inserting Columns based on missing headers

[Dow]

I have a spreadsheet with columns labeled, for example, X1 X2 X3 X4 X5
X6 X7 X8 X9 X10. I need all 10 columns but where this data pulls from
only gives me the columns that have data in the rows below. So if
column X2 is blank it does not pull X2 and would give me X1 X3 X4 X5
X6 X7 X8 X9 X10.

I found this macro for when there is only 1 column missing:

Dim XLCell As Range

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

I can run this 10 times as 10 seperate Subs changing X4 to X1 and then
X2 and etc. to make sure I have all of my columns.

I suspect there is a better way to do this. Does anyone know a macro
that will check row 1 for all of these values and then insert the
missing ones? The location that it inserts them in is not important
(it could even add them onto the end) because I will be sorting them
afterwards.

Thank you for the help,

Dow.

 

[excel-ant]

Hi Dow,

In the following example I've set up a For Next loop to work from left
to right, checking for the appropriate header of X1,X2...X10. If
missing a column is inserted and the header updated as required.

Sub Dow()

' We expect 10 column headers X1 to X10. if one is missing we
insert a column

For i = 1 To 10
If Cells(1, i) <> "X" & i Then
Cells(1, i).EntireColumn.Insert
Cells(1, i) = "X" & i
End If
Next

End Sub

Hopefully this works as you require.

Anthony

 

[Dow]

Hi Dow,

In the following example I've set up a For Next loop to work from left
to right, checking for the appropriate header of X1,X2...X10. If
missing a column is inserted and the header updated as required.

Sub Dow()

    ' We expect 10 column headers X1 to X10. if one is missing we
insert a column

    For i = 1 To 10
        If Cells(1, i) <> "X" & i Then
            Cells(1, i).EntireColumn.Insert
            Cells(1, i) = "X" & i
        End If
    Next

End Sub

Hopefully this works as you require.

Anthony

That is really cool, Anthony. I can see some places where I will be
able to use this concept.

I see now that I should not have used somethng as easy as X1, X2 for
the example of my column headers.

The X1, X2, etc were examples. The actual headings are closer to
"advanced", "redo", "undo", "territories", "box 3". Nothing as nice
and simple as my example.

Do you know of a way to loop through a list of words like this in a
macro?

Dow.

 

[Dow]

HiDow,

In the following example I've set up a For Nextloopto work from left
to right, checking for the appropriate header ofX1,X2...X10. If
missing a column is inserted and the header updated as required.

SubDow()

    ' We expect 10 column headersX1to X10. if one is missing we
insert a column

    For i = 1 To 10
        If Cells(1, i) <> "X" & i Then
            Cells(1, i).EntireColumn.Insert
            Cells(1, i) = "X" & i
        End If
    Next

End Sub

Hopefully this works as you require.

Anthony

Is there no one else out there willing to take a chance on this one?
I realized that I need to do the find with an Array but I still have
not had any luck.

 

[Patrick Molloy]

see my earlier response to your later question

regards