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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.