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?

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



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?

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?

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?

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?

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?

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?

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?