Replace a reference for (as of yet) non existing files

[nmgmarques]

Hi all.

Current file has following formulas in range D2:O8

Column D

=IF(ISERROR(VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-01\MB5L\[MB5L.xlsx]MB5L_Resumo'!$A$1:$H$19;2;FALSE));;VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-01\MB5L\[MB5L.xlsx]MB5L_Resumo'!$A$1:$H$19;2;FALSE))

Column E

=IF(ISERROR(VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-02\MB5L\[MB5L.xlsx]MB5L'!$A$1:$H$19;2;FALSE));;VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-02\MB5L\[MB5L.xlsx]MB5L'!$A$1:$H$19;2;FALSE))

Column F

=IF(ISERROR(VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-03\MB5L\[MB5L.xlsx]MB5l'!$A$1:$H$19;2;FALSE));;VLOOKUP($A2;'K:\MD\_data\Fim do Mês\Data\2018\2018-03\MB5L\[MB5L.xlsx]MB5l'!$A$1:$H$19;2;FALSE))

And so forth. Each column looks for the file MB5L in a subfolder corresponding to the year and the filename has the year and month in format 20xx_xx

This year, we'll pull data from files using the same naming strategy, in a 2019 subfolder. What I am looking to do is replace the 2018's in the formulas with 2019's. Using the find and replace won't work as it asks me to point to the new file (which doesn't exist yet).

Is there any script I can use to replace all references in the range from 2018 to 2019 without the files actually existing?

 

[DanteAmor]

Run this macro:

Sub Replace2018()
    Application.DisplayAlerts = False
    Cells.Replace What:="\2018\2018", Replacement:="\2019\2019", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[nmgmarques]

Run this macro:

Sub Replace2018()
    Application.DisplayAlerts = False
    Cells.Replace What:="\2018\2018", Replacement:="\2019\2019", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

That did the trick. Thanks!

 

[DanteAmor]

Youre welcome