Code to FIND a value in column A then insert a row above it 1

[ajaeger]

I have several hundred excel files and I need to insert a row above a certain value. For example, the files look like:

Column A
1-Company
2-Affiliates
ABC Affiliate
DEF Affiliate
3-Company Description
4-Committee Affiliation

Since different .xls files may have different numbers of affiliates, I can't code it to select row 6 ("4-Committee Affiliation) and insert a row above it - in some files "4-Committee Affiliation" may be in row 5 or even row 10. Instead, I need to FIND "4-Committee Affiliation", select that row and insert a row.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[SkipVought]

Hi,

Turn on your macro recorder and record finding that value and then inserting a row.

Post back with your recorded code for help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ajaeger]

Here it is...I just have to find the first instance of "1-company contact" and insert the row above it.

Cells.Find(What:="1-company contact", After:=ActiveCell, LookIn:= xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= xlNext, MatchCase:=False, SearchFormat:=False).Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[SkipVought]

Check this out...

Dim rFound as range

set rfound = Cells.Find(What:="1-company contact")

If not rfound is nothing then
   rfound.Insert Shift:=xlDown
end if

If this works, then we can look at replicating in a loop

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ajaeger]

Works, but it inserts a row (ie, bumps column A down) instead of bumping the entire row down.

I don't think I need to loop, just need to copy over for 2 other phrases that I'm looking for.

Thanks!

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[SkipVought]

Dim rFound as range

set rfound = Cells.Find(What:="1-company contact")

If not rfound is nothing then
   rfound[b].EntireRow.[/b]Insert Shift:=xlDown
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ajaeger]

Thank you! That did it.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com