Macro to replace known static by entering only replacement text input box

**Dbezel** · 11-26-2017, 06:21 PM

Hi all!

Looking for some help with a macro to replace/populate text that is already in a worksheet with user input without the user having to input the known text, just the replacement text.

I have a worksheet with multiple cells containing strings of static text that I am using as a template.

For instance, one cell may read "APEX "State Abbreviation" "City" Local Team "Team Number"

The words "State Abbreviation", "city", and "team number" appear multiple times throughout the document and I'd like to replace each one with the same input.

I was using the below, but am stuck on how to either hide that first input box or enter the known text string phrases as replacement code.

Any help would be greatly appreciated!!!

Sub rpl()
Sheets("sheet1").Select
What = InputBox("Copy "State Abbreviation" into the box")
repl = InputBox("State Abbreviation")
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sub rpl()
Sheets("sheet1").Select
What = InputBox("Copy "City" into the box")
repl = InputBox("City")
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sub rpl()
Sheets("sheet1").Select
What = InputBox("Copy "Team Number" into the box")
repl = InputBox("Team Number")
Cells.Replace What:=What, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

**Kaper** · 11-27-2017, 03:05 AM

Do both:
1) try:

Sub rpl()

Sheets("sheet1").Select
dim sWhat as string
sWhat = """State Abbreviation"""
repl = InputBox("State Abbreviation")
Cells.Replace What:=sWhat, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
sWhat = """City"""
repl = InputBox("City")
Cells.Replace What:=sWhat, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
sWhat = """Team Number"""
repl = InputBox("Team Number")
Cells.Replace What:=sWhat, Replacement:=repl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

2) Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Macro to replace known static by entering only replacement text input box

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to replace known static by entering only replacement text input box

Re: Macro to replace known static by entering only replacement text input box

Thread Information

Users Browsing this Thread

Similar Threads

Macro for same text replacement on multiple sheets of a workbook

Outlook Text Replacement Macro

Excel VB Macro- Literal Text Replacement

Macro to calculate the percent difference after entering an input

Using text box input to lookup number and replace based on user input into new column

Using macro to input static date

Use a static set of data for input into a macro

Bookmarks

Bookmarks

Posting Permissions