Macro to insert headers..need coding help

[MaryLindholm]

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?

 

[dolivastro]

I think one problem you are having is the statement: "Cells.Insert
shift:=xlDown". You need to give the exact cell location where the
insert is to take place. Eg, "Cells(5,1).Insert shift := xlDown"

BTW, why are you using "FormulaR1C1"? Shouldn't you just use "value"
instead?


Hope this helps,
Dom

 

[Sandy]

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

 

[MaryLindholm]

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]

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?

 

[MaryLindholm]

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]

Glad you got it...

Sandy

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.

 

[MaryLindholm]

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.

 

[MaryLindholm]

But it doesn't loop and do headers on all the 02s.

 

[Art H]

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