Finding Value on sheet and inserting row under it

[masterbuddha]

Hi can you please help me with a problem?

I am trying to make a macro that searches for a value in a column and once it finds it inserts a new row and entry under it.

Eg. Find Bob and insert Emily(Wife) under Bob

1. Bob

2. Bob
____ (new row)

3. Bob
Emily


Any help would be great thank you.

[Jacob Hilderbrand]

This should get you started:

[vba]
Option Explicit

Sub FindAndInsert()

Dim Cel As Range
Dim SearchFor As String
Dim InsertText As String
Dim Row As Long

SearchFor = "Bob"
InsertText = "Emily"
Set Cel = Range("A:A").Find(What:=SearchFor, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cel Is Nothing Then
Row = Cel.Row + 1
Range("A" & Row).EntireRow.Insert
Range("A" & Row).Value = InsertText
End If

ExitSub:

Set Cel = Nothing

End Sub
[/vba]

But I am not sure how you want to get the values for "Bob" and "Emily". You could use Input Boxes for example.

Also, do you need to worry about multiple matches?

[masterbuddha]

Ah thank you that works fine for me

The rest of it i can understand, i just couldn't work out a piece of code that would work.

Thanks heaps for the reply.