Excel Find and Replace paste in multiple cells

[shekhu]

Hi,

I am new to excel macros and I am trying to use the following code.

The issue I have is with replace part. When I put in "Client Name" I want that to be replaced by "Client NameNextCellAddressNextCellemail

What I am getting is, everything in the same cell itself.

If anybody could help me out, it would be great.
Thanks

Sub FindAndReplace()
'
Selection.Replace What:="Petition #: ", Replacement:="PN", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="Email: ", Replacement:="E", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="Fax : ", Replacement:="F", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="Debtor disposition:**", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="Client Name", Replacement:="Client Name^tAddress^temail", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="ST", Replacement:="STREET", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="STS", Replacement:="STREETS", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=True
End Sub

[Benzadeus]

I don't know if I fully understand what you wanted, but try something like this:
Sub MultipleReplacement()
With Cells.Find("client name to search")
.Offset = "replacement name"
.Offset(, 1) = "replacement address"
.Offset(, 2) = "replacement email"
End With
End Sub

[shekhu]

I basically want to find and replace many words as given in the sample code, apart from that I also like to replace Client Name with "Client Name Address email" Everything else is functioning okay, but when it replaces Client Name, this comes in a single cell, i need it in different cells such as
"Client Name" "Address" "email"

[Benzadeus]

I don't know if I got it... you need to concatenate information, right?
Sub MultipleReplacement()
With Cells.Find("client name to search")
.Offset = .Offset & .Offset(, 1) & .Offset(, 2)
End With
End Sub

[shekhu]

Hello Benzadeus, thanks for your valuable feedback.

Your code posted at #2 is okay and it works fine.
For me, it has issues, what if there are many clients, eg., client1, client2, client3, client4, etc.
Then I need
client1 changes to name1 address1 email1
client2 changes to name2 address2 email2
client3 changes to name3 address3 email3
and so on
If this is done, this might serve my purpose to a great extent.

Thanks again.

[shekhu]

For your ease I am attaching you an image, explaining what I am looking for. The information we will put in the macro before hand and run the macro.
where ever the name is found, it replaces with name address and email in three different cells.

The code you suggested at #2 is little bit close to it.

Looking forward for a help, thanks.

[Benzadeus]

Somehow you have to link your Client's name to a database see the structure I used as example in the picture.
The VBA code is:
Sub MultipleReplace()

Dim rngWhere As Range
Dim rngData As Range
Dim rng As Range
Dim rngSearch As Range

Set rngWhere = Range("A2:A4")
Set rngData = Range("C8:C11")

For Each rng In rngData
Set rngSearch = rngWhere.Find(rng, , , xlWhole)
If Not rngSearch Is Nothing Then
rng.Offset(, 1).Resize(, 3).Copy Destination:=rngSearch
End If
Next rng

End Sub

[shekhu]

Thanks Benzadeus for your assistance. Can I use a separate excel worksheet for database, which I already have, from where the macro could pick the data. How to put the path of that database excel?

[Benzadeus]

You said Worksheets, right? Or would it be another workbook?
You just can qualify the range as showed below:
Set rngWhere = Sheets("ClientsList").Range("A2:A4")
Set rngData = Sheets("Database").Range("C8:C11")