Finding and Replacing Name

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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)?
 
Upvote 0
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
 
Upvote 0
Is there any need to actually store the date in E1? Can get the information from an input box instead
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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