About Us

Dee Sperling[_2_]

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

.

.

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
insert rows and copying cells in column b,c, and d into new rows	gbpg	Excel Programming	0	December 31st 09 12:34 AM
FInd value and insert rows below	vlad	Excel Programming	1	September 20th 07 11:22 AM
find number & insert rows	Steve Wallis	Excel Programming	4	March 21st 06 05:48 PM
Need Macro to Find Column Heading -- if none, then insert new column	Jeff[_43_]	Excel Programming	0	December 15th 04 07:08 AM
find row value, insert column	gaba	Excel Programming	0	October 18th 04 06:59 PM

Menu

About Us