Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
Hi. I have some very large spread sheets that I need to insert headers
into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
Try running this code on a spare sheet(copy your original for testing
purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
Thank you for that. But this only seems to take care of if there is 1
row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
In my trials this code inserted a row(in the active sheet) above each
cell that had a "01" in column "A". It then placed the required headers in each column in each inserted row. What did it do when it was run? Are you looking to do this on one sheet or the whole workbook? wrote: Thank you for that. But this only seems to take care of if there is 1 row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
well, actually i cheated and copied it a second time for anything that
had an 02 ElseIf Cells(Counter, 1) = 2 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Customer Number" Cells(Counter, 3).Value = "Employee ID" Cells(Counter, 4).Value = "Employee ID Type" Cells(Counter, 5).Value = "First Name" Cells(Counter, 6).Value = "Middle Name" Cells(Counter, 7).Value = "Last Name" Cells(Counter, 8).Value = "Street Address 1" Cells(Counter, 9).Value = "Street Address 2" Cells(Counter, 10).Value = "Street Address 3" and that is the part that didn't put a header over any row that had an 02 it just did it once. Sandy wrote: In my trials this code inserted a row(in the active sheet) above each cell that had a "01" in column "A". It then placed the required headers in each column in each inserted row. What did it do when it was run? Are you looking to do this on one sheet or the whole workbook? wrote: Thank you for that. But this only seems to take care of if there is 1 row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
Glad you got it...
Sandy wrote: well, actually i cheated and copied it a second time for anything that had an 02 ElseIf Cells(Counter, 1) = 2 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Customer Number" Cells(Counter, 3).Value = "Employee ID" Cells(Counter, 4).Value = "Employee ID Type" Cells(Counter, 5).Value = "First Name" Cells(Counter, 6).Value = "Middle Name" Cells(Counter, 7).Value = "Last Name" Cells(Counter, 8).Value = "Street Address 1" Cells(Counter, 9).Value = "Street Address 2" Cells(Counter, 10).Value = "Street Address 3" and that is the part that didn't put a header over any row that had an 02 it just did it once. Sandy wrote: In my trials this code inserted a row(in the active sheet) above each cell that had a "01" in column "A". It then placed the required headers in each column in each inserted row. What did it do when it was run? Are you looking to do this on one sheet or the whole workbook? wrote: Thank you for that. But this only seems to take care of if there is 1 row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
This is how I edited it:
Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" ElseIf Cells(Counter, 1) = 2 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub The values would be different in 02 in reality but I just wanted to get it working first. wrote: well, actually i cheated and copied it a second time for anything that had an 02 and that is the part that didn't put a header over any row that had an 02 it just did it once. Sandy wrote: In my trials this code inserted a row(in the active sheet) above each cell that had a "01" in column "A". It then placed the required headers in each column in each inserted row. What did it do when it was run? Are you looking to do this on one sheet or the whole workbook? wrote: Thank you for that. But this only seems to take care of if there is 1 row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
But it doesn't loop and do headers on all the 02s.
Mary Sandy wrote: Glad you got it... Sandy wrote: well, actually i cheated and copied it a second time for anything that had an 02 ElseIf Cells(Counter, 1) = 2 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Customer Number" Cells(Counter, 3).Value = "Employee ID" Cells(Counter, 4).Value = "Employee ID Type" Cells(Counter, 5).Value = "First Name" Cells(Counter, 6).Value = "Middle Name" Cells(Counter, 7).Value = "Last Name" Cells(Counter, 8).Value = "Street Address 1" Cells(Counter, 9).Value = "Street Address 2" Cells(Counter, 10).Value = "Street Address 3" and that is the part that didn't put a header over any row that had an 02 it just did it once. Sandy wrote: In my trials this code inserted a row(in the active sheet) above each cell that had a "01" in column "A". It then placed the required headers in each column in each inserted row. What did it do when it was run? Are you looking to do this on one sheet or the whole workbook? wrote: Thank you for that. But this only seems to take care of if there is 1 row that starts with 01...I have various ones through out the file. Do I need to put a loop command in? Sandy wrote: Try running this code on a spare sheet(copy your original for testing purposes). I think this is what you were looking to accomplish: Sub test() Dim Counter As Integer For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1 If Cells(Counter, 1) = 1 Then Cells(Counter, 1).EntireRow.Insert Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).Value = "Process Date/Time" Cells(Counter, 3).Value = "Customer Number" Cells(Counter, 4).Value = "Customer Name" Cells(Counter, 5).Value = "Enrollment Type" Cells(Counter, 6).Value = "Filler" End If Next Counter End Sub Sandy wrote: Hi. I have some very large spread sheets that I need to insert headers into. I figure I can do an If...then statement but I'm not getting it to work. Here is what I have: Sub Headers() Dim Counter As Double Counter = 1 If Cells(Counter, 1) = "01" Then Cells.Insert shift:=xlDown 'do the splitting here, like Cells(Counter, 1).FormulaR1C1 = "Record Type" Cells(Counter, 2).FormulaR1C1 = "Process Date/Time" Cells(Counter, 3).FormulaR1C1 = "Customer Number" Cells(Counter, 4).FormulaR1C1 = "Customer Name" Cells(Counter, 5).FormulaR1C1 = "Enrollment Type" Cells(Counter, 6).FormulaR1C1 = "Filler" End If 'Increment the Counter By 1 Counter = Counter + 1 End Sub So every time the first cell has an 01 in it I want to insert a row that has the header titles in it. However, when I run this macro nothing happens. I'm not sure what I am missing. Can you help? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert headers..need coding help
Mary,
Do the cells that are to act as triggers to insert the header contain text (i.e., "02")? If so then change the test from if cells(counter,1)=2 to if cells(counter,1)="02" Also, is the intent to process one worksheet at a time? or one workbook with more than one worksheet and process all of the worksheets within the one workbook? If processing all worksheets within one workbook, you need to loop through all worksheets making each worksheet active in turn then run the test for the "01" and "02" as Cells only works on the active worksheet. The following is an excerpt from Excel 2000 help for the Cells property: "Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet." You might consider something like For Each ws In ActiveWorkbook.Worksheets If ws.Cells(counter, 1) = "02" Then AddHeader2 Next HTH, Art |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Way to insert a footer in an entire workbook w/o altering headers | Excel Discussion (Misc queries) | |||
Insert work sheet with Custom headers | Excel Discussion (Misc queries) | |||
Implant macro coding into ASP coding | Excel Programming | |||
VBA-How to insert vba code file in a project using coding rather than manual import | Excel Programming | |||
Coding a button or hyperlink to insert a predfined row | Excel Programming |