Finding and Replacing Name

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,973
Office Version
  1. 2019
Platform
  1. Windows
I have a spreadsheet that is linked to another spreadheet. Each month I need the name "Template" replaced with another name for eg Sept2006, Oct2006 etc.

The worksheet name is F & I Comm.

The range will never Exceed A1:Z200.


I feel that it would be easier to set up a prompt that will force the user to enter the month and year into E1 and then to use the data in E1 to replace the name template (full name of original file ='[MONTH END.New Vehicles.Template.xls]STATS'!$G$15-Only the word template to be replaced)

I need to set up VBA code force the user to enter the Month and Year and then will use the Month and Year that has now been entered into E1 to replace the word template

Your assistance will be most appreciated

Howard
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Does the user need to enter the date every time they open the spreadsheet. If not how do you know that the date needs to be entered?

To clarify. You need every instance of the word "template" that is in a formula to be replaced by the correct month and date combination. I notice you have both Sept and Oct. Will the user know the correct format for the month (ie 3 or 4 letters)?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,973
Office Version
  1. 2019
Platform
  1. Windows
When the user needs to enter the date, the user will activate the Macro that will prompt the user to enter the Month and year in cell E1, or alternatively the user can manually enter the date into Cell E1.

The user will need to change the date every month. For eg if the work relates to Sept2006 the user will enter Sept2006 into Cell E1

The word Template needs to be replaced every instance by the month and year entered into cell E1.

It may be easier to have once set of VBA code that prompts the user to enter the month and Enter, which will go into Cell E1 and then to replace the word template in every instance by the montrh and year in cell E1

Your assistance will be much appreciated

Howard
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Is there any need to actually store the date in E1? Can get the information from an input box instead
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
This is an easy way to do it with an input box. If you want you can make Range E1 equal to the input as well

Code:
Sub ReplaceString()
    
    Dim strInput As String
    
    Const strCode As String = "Template" 'string of code to search for
    
    strInput = InputBox("Please enter the date to replace")
       
    
    Sheets("F & I Comm").Range("A1:Z200").Replace what:=strCode, replacement:=strInput
    
    'Add date into sheet if required (remove the apostrophe)
    'Sheets("mySheetName").range("E1")=strInput
      
    
End Sub
 

Forum statistics

Threads
1,136,926
Messages
5,678,618
Members
419,776
Latest member
mikelowski

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
Top