Replace formula's file

[Panoos64]

Hi all, i would like to write a Vba, so that to run through col. "C", "D", "E", "F" and where find the formulas which contains the file OCCUPANCY 2017, should replace it with OCCUPANCY 2018 but with Input Box in which i will write the file.

Thanking you in advance

 

[davesexcel]

You can use the find/replace that excel has.
select eh range of cells, hit Ctrl & H
Find what "OCCUPANCY 2017", replace with what "OCCUPANCY 2018", you may have to go into options and have formulas selected.

If it works and you still want a code for doing that, then use the macro recorder.

 

[Panoos64]

Thank you dave. It works your above command and is an effective idea. Just i prefer an "Input Box" so that to write by manual method the file which i will replace. If i recorder the above, it will be a stable command. I need to change the file any time. Is it possible the above, to record it using "Input Box" ? I would be greatly appreciate if you could help me. Thanks once again!

 

[davesexcel]

Show the code so it can be edited for you.

 

[Panoos64]

Below is the code. I recorded as you explain me but i prefer the replacement option to be valid through "Input Box". Thanks

Range("C9:L252").Select
Cells.Replace What:="OCCUPANCY", Replacement:="OCCUPANCY 2018", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[davesexcel]

This should do it

    Dim fnd As String
    Dim rplc As String
    fnd = InputBox("Find what?")
    rplc = InputBox("Replace with?")

    Range("C:L").Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
                             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False

 

[Panoos64]

Many thanks dave. your code works too. Thanks once again for your support. Have a nice day