Find value in a column and insert rows above

[Dee Sperling]

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

 

[Don Guillett]

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.

 

[Bob Umlas, Excel MVP]

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]

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?

 

[Dee Sperling]

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

 

[Dee Sperling]

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

 

[Rick Rothstein]

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.

 

[Dee Sperling]

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]

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

 

[Dee Sperling]

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]

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 Rothstein]

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

 

[Dee Sperling]

Perfect! Thank you so much!

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)




.