Find value in a column and insert rows above
 

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dee Sperling" wrote in message
...
The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this
order.
I need to find the first Y in each column and insert 2 rows above that
row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

You didn't specify the "rules" for what titles to use, so this routine does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

The titles will be New, Old, Existing, Deleted in that order from U to X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this routine does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

Would help if I said what line it stops on:
rw = Columns(col).Find("Y").Row


"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this routine does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U to X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this routine does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this order.
I need to find the first Y in each column and insert 2 rows above that row.
On the blank row above the first Y, I need to highlight in yellow and put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

As structured, the code expects you to have the worksheet with your Y's and
N's as the active worksheet. Go back and select that worksheet and then try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U to X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this routine
does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this
order.
I need to find the first Y in each column and insert 2 rows above
that row.
On the blank row above the first Y, I need to highlight in yellow and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

Find value in a column and insert rows above
 

It is running on the active sheet, it goes once and inserts a cell rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your Y's and
N's as the active worksheet. Go back and select that worksheet and then try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U to X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this routine
does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in this
order.
I need to find the first Y in each column and insert 2 rows above
that row.
On the blank row above the first Y, I need to highlight in yellow and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

Find value in a column and insert rows above
 

I do not get that error when running the posted code on my system. Did you
copy/paste it or retype it? If the later, perhaps you introduced a typo.

Anyway, here is Bob's code modified to insert whole rows and I also added a
mechanism to put your actual titles in the inserted rows; just assign the
comma delimited string of titles to the Titles constant (in the Const
statement) and do NOT use spaces around the commas in an attempt to "neaten"
things up...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 21)
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It is running on the active sheet, it goes once and inserts a cell rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your Y's
and
N's as the active worksheet. Go back and select that worksheet and then
try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U to
X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this
routine
does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in
this
order.
I need to find the first Y in each column and insert 2 rows above
that row.
On the blank row above the first Y, I need to highlight in yellow
and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

Find value in a column and insert rows above
 

I copied it directly. So took your code and copied to Notepad, then into VB.
It works great, except if there is no Y in a column. That's when it stops
at the
rw = Columns(col).Find("Y").Row

How would I fix that?

Thanks again.

"Rick Rothstein" wrote:

I do not get that error when running the posted code on my system. Did you
copy/paste it or retype it? If the later, perhaps you introduced a typo.

Anyway, here is Bob's code modified to insert whole rows and I also added a
mechanism to put your actual titles in the inserted rows; just assign the
comma delimited string of titles to the Titles constant (in the Const
statement) and do NOT use spaces around the commas in an attempt to "neaten"
things up...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 21)
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It is running on the active sheet, it goes once and inserts a cell rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your Y's
and
N's as the active worksheet. Go back and select that worksheet and then
try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U to
X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this
routine
does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in
this
order.
I need to find the first Y in each column and insert 2 rows above
that row.
On the blank row above the first Y, I need to highlight in yellow
and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

.

Find value in a column and insert rows above
 

Sorry... my fault... I made an assumption that there would always be at
least one Y in each column. Give this code a try and see if it does what you
want...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
On Error Resume Next
For col = 1 To 4
rw = Columns(col).Find("Y").Row
If Err.Number = 0 Then
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 1)
Else
Err.Clear
End If
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
I copied it directly. So took your code and copied to Notepad, then into
VB.
It works great, except if there is no Y in a column. That's when it stops
at the
rw = Columns(col).Find("Y").Row

How would I fix that?

Thanks again.

"Rick Rothstein" wrote:

I do not get that error when running the posted code on my system. Did
you
copy/paste it or retype it? If the later, perhaps you introduced a typo.

Anyway, here is Bob's code modified to insert whole rows and I also added
a
mechanism to put your actual titles in the inserted rows; just assign the
comma delimited string of titles to the Titles constant (in the Const
statement) and do NOT use spaces around the commas in an attempt to
"neaten"
things up...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 21)
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It is running on the active sheet, it goes once and inserts a cell
rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your
Y's
and
N's as the active worksheet. Go back and select that worksheet and
then
try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in
message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from U
to
X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this
routine
does
what you asked byt just puts the word "Title" in the yellow cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted in
this
order.
I need to find the first Y in each column and insert 2 rows
above
that row.
On the blank row above the first Y, I need to highlight in
yellow
and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

.

Find value in a column and insert rows above
 

Sorry, I used my test limits for the columns instead of the columns you
wanted. Change the For statement to this...

For col = 21 To 24

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Sorry... my fault... I made an assumption that there would always be at
least one Y in each column. Give this code a try and see if it does what
you want...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
On Error Resume Next
For col = 1 To 4
rw = Columns(col).Find("Y").Row
If Err.Number = 0 Then
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 1)
Else
Err.Clear
End If
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
I copied it directly. So took your code and copied to Notepad, then into
VB.
It works great, except if there is no Y in a column. That's when it
stops
at the
rw = Columns(col).Find("Y").Row

How would I fix that?

Thanks again.

"Rick Rothstein" wrote:

I do not get that error when running the posted code on my system. Did
you
copy/paste it or retype it? If the later, perhaps you introduced a typo.

Anyway, here is Bob's code modified to insert whole rows and I also
added a
mechanism to put your actual titles in the inserted rows; just assign
the
comma delimited string of titles to the Titles constant (in the Const
statement) and do NOT use spaces around the commas in an attempt to
"neaten"
things up...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 21)
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It is running on the active sheet, it goes once and inserts a cell
rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your
Y's
and
N's as the active worksheet. Go back and select that worksheet and
then
try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in
message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from
U to
X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this
routine
does
what you asked byt just puts the word "Title" in the yellow
cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted
in
this
order.
I need to find the first Y in each column and insert 2 rows
above
that row.
On the blank row above the first Y, I need to highlight in
yellow
and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

.

Find value in a column and insert rows above
 

Perfect! Thank you so much!

"Rick Rothstein" wrote:

Sorry, I used my test limits for the columns instead of the columns you
wanted. Change the For statement to this...

For col = 21 To 24

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Sorry... my fault... I made an assumption that there would always be at
least one Y in each column. Give this code a try and see if it does what
you want...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
On Error Resume Next
For col = 1 To 4
rw = Columns(col).Find("Y").Row
If Err.Number = 0 Then
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 1)
Else
Err.Clear
End If
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
I copied it directly. So took your code and copied to Notepad, then into
VB.
It works great, except if there is no Y in a column. That's when it
stops
at the
rw = Columns(col).Find("Y").Row

How would I fix that?

Thanks again.

"Rick Rothstein" wrote:

I do not get that error when running the posted code on my system. Did
you
copy/paste it or retype it? If the later, perhaps you introduced a typo.

Anyway, here is Bob's code modified to insert whole rows and I also
added a
mechanism to put your actual titles in the inserted rows; just assign
the
comma delimited string of titles to the Titles constant (in the Const
statement) and do NOT use spaces around the commas in an attempt to
"neaten"
things up...

Sub InsertY()
Const Titles As String = "New,Old,Existing,Deleted"
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).EntireRow.Insert
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = Split(Titles, ",")(col - 21)
Next
End Sub

--
Rick (MVP - Excel)



"Dee Sperling" wrote in message
...
It is running on the active sheet, it goes once and inserts a cell
rather
than a row, then stops with the error.
The result in the spreadsheet looks like this:
New Old Existing Deleted
N N N
Title N N N
Y N N N
Y N N N
Y N N N
N Y N N
N N Y N
N N N Y
N N N Y


"Rick Rothstein" wrote:

As structured, the code expects you to have the worksheet with your
Y's
and
N's as the active worksheet. Go back and select that worksheet and
then
try
running the code again.

--
Rick (MVP - Excel)



"Dee Sperling" wrote in
message
...
It would probably help if I specified which line it stops on:
rw = Columns(col).Find("Y").Row

"Dee Sperling" wrote:

The titles will be New, Old, Existing, Deleted in that order from
U to
X.
Sorry I didn't make that clearer.

I put the code in my module but I get Run-time errro '91":
Opject variable or With block variable not set

I'm using Excel 2003, is that the cause?

"Bob Umlas, Excel MVP" wrote:

You didn't specify the "rules" for what titles to use, so this
routine
does
what you asked byt just puts the word "Title" in the yellow
cell:

Sub InsertY()
For col = 21 To 24
rw = Columns(col).Find("Y").Row
Cells(rw, col).Resize(2).Insert shift:=xlDown
Cells(rw + 1, col).Interior.Color = vbYellow
Cells(rw + 1, col).Value = "Title"
Next
End Sub

Bob Umlas
Excel MVP

"Dee Sperling" wrote:

The set up looks like this:
ColU ColV ColW ColX
Y N N N
Y N N N
N Y N N
N N Y N
N N Y N
N N Y N
N N Y N
N N Y Y

Columns will always be U through X and will always be sorted
in
this
order.
I need to find the first Y in each column and insert 2 rows
above
that row.
On the blank row above the first Y, I need to highlight in
yellow
and
put
title in the first cell, such as New, Old, Existing, Deleted.

Any help would be greatly appreciated.

Thanks for your time,
Dee

.

.

.