Replace in a formula

[vabulden]

Hello! I'm looking for a macro that can look in a formula for a specific text and change it.
Example:
I have a formula "=DATE(2013,2,1)" which is part of a complex formula
I need to change it to "=DATE(YEAR(A$1);MONTH(A$1);1)"
When I record the macros I receive:

Cells.Replace What:="2013,2", Replacement:="year(a$1);month(a$1)", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

But when i run the code it doesn't work.

 

[patel45]

please upload a sample file on a share site then paste the link here

 

[Peter_SSs]

Hello! I'm looking for a macro that can look in a formula for a specific text and change it.
Example:
I have a formula "[highlight]=DATE(2013,2,1)[/highlight]" which is part of a complex formula
I need to change it to "[highlight]=DATE(YEAR(A$1);MONTH(A$1);1)[/highlight]"
When I record the macros I receive:

Cells.Replace What:="2013,2", Replacement:="year(a$1);month(a$1)", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

But when i run the code it doesn't work.

Welcome to the MrExcel board!

In the one part you have commas as separators but in the other part you have semicolons.
One of those formats will be invalid on your system and I think that is the issue.

 

[vabulden]

Made a quick example file: replaceformula.xlsm download - 2shared

Peter, I use Russian version of Excel and we have different syntax. I tried to change the local names of objects and functions but it doesn't help

 

[patel45]

VBA uses comma instead of semicolon also in my italian version

Cells.Replace What:="(2013,2", Replacement:="(year(k$3),month(k$3)", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[vabulden]

Thanks for help, it works)