Find & Replace cells

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi ,

I have the following table with new names and old names and i would like to find and replace all the names in the workbook (Sheet1,2,3....) with reference to this table. The table is stored in "Rename" sheet.

<table border="0" cellpadding="0" cellspacing="0" width="180"><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:77pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="103">A1
</td> <td style="width:58pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="77">B1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">OldName</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">NewName</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">APPLE</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">STAPLE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">BALL</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">BAT</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">CAT</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">DOG</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">HONDA</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">SUZUKI</td> </tr> </tbody></table>
The range of old names and new names will not be greater than 2000

Can any one help me with VBA?

Thank you
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
try this


'
Range("A2").Select
Selection.Copy
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Sheets("Sheet1").Activate
Cells.Replace What:="APPLE ", Replacement:="STAPLE", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="BALL ", Replacement:="BAT", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="CAT ", Replacement:="DOG", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="HONDA ", Replacement:="SUZUKI", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("rename sheet").Select

Sheets("Sheet2").Activate
Cells.Replace What:="APPLE ", Replacement:="STAPLE", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="BALL ", Replacement:="BAT", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="CAT ", Replacement:="DOG", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="HONDA ", Replacement:="SUZUKI", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("rename sheet").Select

Sheets("Sheet3").Activate
Cells.Replace What:="APPLE ", Replacement:="STAPLE", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="BALL ", Replacement:="BAT", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="CAT ", Replacement:="DOG", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="HONDA ", Replacement:="SUZUKI", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("rename sheet").Select
End Sub
 
Upvote 0
This is my own version I used before almost the same as your requirement:
Code: 
Sub ChangeData()
Application.ScreenUpdating = False
Application.EnableEvents = False
With WorksheetFunction.Application
Set myR = Range("A:A") 'Change range to suit
lrow = Cells(Rows.Count, "A").End(xlUp).Row
Search:
        prompt = "Which data you wish to change?"
        Title = "Change"
        updrec = InputBox(prompt, Title)
        If StrPtr(updrec) = 0 Then
            Exit Sub
        ElseIf updrec = "" Then
            Exit Sub
        Else
            For i = 2 To lrow
                If Not IsError(.Index(myR, .Match(updrec, myR, 0), 1)) = True Then
                    updval = InputBox("Current Data: " & Range("A" & .Match(updrec, myR, 0)) & _
                            vbNewLine & "Enter New Data:", "New Data")
                    Range("B" & .Match(updrec, myR, 0)).Value = updval
                    MsgBox "Data: " & "[" & updrec & "]" & " successfully changed to " & _
                                "[" & updval & "]", vbInformation + vbOKOnly, "Update"
                    Exit For
                Else
                    ask = MsgBox("Data not found. Do you want to search another?", vbYesNo, "No Data Found")
                    If ask = vbYes Then
                        GoTo Search
                    Else: Exit For
                    End If
                
                End If
            Next
        End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End With
End Sub
 
Upvote 0
Thank you verymuch Andreas and Villy

Your help is very much appreciated

Have a nice day
 
Upvote 0
villy said:
This is my own version I used before almost the same as your requirement:
Code: 
Sub ChangeData()
Application.ScreenUpdating = False
Application.EnableEvents = False
With WorksheetFunction.Application
Set myR = Range("A:A") 'Change range to suit
lrow = Cells(Rows.Count, "A").End(xlUp).Row
Search:
        prompt = "Which data you wish to change?"
        Title = "Change"
        updrec = InputBox(prompt, Title)
        If StrPtr(updrec) = 0 Then
            Exit Sub
        ElseIf updrec = "" Then
            Exit Sub
        Else
            For i = 2 To lrow
                If Not IsError(.Index(myR, .Match(updrec, myR, 0), 1)) = True Then
                    updval = InputBox("Current Data: " & Range("A" & .Match(updrec, myR, 0)) & _
                            vbNewLine & "Enter New Data:", "New Data")
                    Range("B" & .Match(updrec, myR, 0)).Value = updval
                    MsgBox "Data: " & "[" & updrec & "]" & " successfully changed to " & _
                                "[" & updval & "]", vbInformation + vbOKOnly, "Update"
                    Exit For
                Else
                    ask = MsgBox("Data not found. Do you want to search another?", vbYesNo, "No Data Found")
                    If ask = vbYes Then
                        GoTo Search
                    Else: Exit For
                    End If
                
                End If
            Next
        End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End With
End Sub
Click to expand...

Hi Villy ,

Could you explain how this macro works ? and what should i enter in the input box?

I have the table ( Old Names and New Names ) in a sheet1 and i wish to change the oldnames in other sheets of the same workbook with reference to the table in sheet1.

Thank you for the help
 
Upvote 0
I don't think if I get your question correctly but this works like if you have a series of datas in Col A and you wanto to find and replace that data it will store the new data into Col B opposite the old ones.
Try to run this and you will be guided accordingly.

I read again your post and I get different view, I think what you have is a table with datas, sheet1,sheet2 etc... that contains the old names to be change based on the new names on the table..Is that right? If yes then I am working on it.
 
Upvote 0
Perhaps I didn't present my question properly.

Well, I have to change the Old names with new names in all the available worksheets in a single workbook.

If any old name ( With Ref to Table) is found in any sheet...sheet1,2,3,etc...it should get changed to new name.

you may assume that the Table ( Old names and New names ) is stored in a separate sheet .

Can you kindly work on this situation?

Thank you villy
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
R
  • Question Question
Random
Replies
1
Views
580
Fluff
Fluff

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top