Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Columns based on missing headers
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Columns based on missing headers
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Columns based on missing headers
On May 14, 1:13*pm, excel-ant wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Columns based on missing headers
On May 14, 1:13*pm, excel-ant wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Columns based on missing headers
see my earlier response to your later question
regards "Dow" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Columns Based on List | Excel Discussion (Misc queries) | |||
Missing Column Headers / Can't scroll to top | Excel Discussion (Misc queries) | |||
Inserting text into custom headers / footers | Excel Discussion (Misc queries) | |||
Missing Column and Row Headers? | Excel Discussion (Misc queries) | |||
Inserting column headers | Excel Programming |