OzGrid

How to Find and replace based on list entered by user input

< Back to Search results

 Category: [Excel]  Demo Available 

How to Find and replace based on list entered by user input

 

Requirement:

 

The user wants to know if you generate an InputBox that will accept a list of names (perhaps delimited) that will then search a worksheet and replace all the names on the list with a single name entered in a second input?

 

Here is a simple example. There will be additional information in subsequent columns after column A, but the user does not want any of this data to change.

Ideally a user will open the workbook and execute the macro causing an input box to appear asking the user the names to be replaced (to which they can copy and paste a group of names as shown below).

Smith, John
Hall, Joe
Himes, Steve

OR

Delimited such as...

Smith, John; Hall, Joe; Himes, Steve

A Second field will ask for the replacement name

Myers, Donnie


End result would be the following list (with no changes aside from Column A).

Myers, Donnie
Myers, Donnie
Foor, Mike
Wilk, Dave
Myers, Donnie


These lists have the potential to contain hundreds of entries, including repeated names.

 

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148344-find-and-replace-based-on-list-entered-by-user-input

 

Solution:

 

Try this VBA code. Make sure that there are no spaces between the names after you insert the pipe character (|) in the input box. ie. Smith, John;Hall, Joe

Code:
Option Explicit
 
Sub FindX()
Dim myArray As Variant
Dim arr As String
arr = InputBox("What names to look for?  Enter each delimeted with a ""|"".")
Dim Repl As String
Repl = InputBox("What is the replacement Name?")
Dim c As Range, rng As Range, lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:A" & lr)
Dim i As Long
myArray = Split(arr, "|")
For i = LBound(myArray) To UBound(myArray)
    For Each c In rng
        c.Replace What:=myArray(i), Replacement:=Repl, _
                  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                  SearchFormat:=False, ReplaceFormat:=False
    Next c
Next i


MsgBox "complete"


End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to use VBAs Find Function
How to find sequence of a column with duplicates
How to find the least negative value in an array
How to find minimum values

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)