PDA

View Full Version : Finding Value on sheet and inserting row under it



masterbuddha
02-28-2006, 09:59 PM
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. :friends:

Jacob Hilderbrand
02-28-2006, 11:27 PM
This should get you started:


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


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
02-28-2006, 11:56 PM
Ah thank you that works fine for me ;)

:rotlaugh: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.

:bow: