Changing a portion of formula using excel vba

[firetrue]

Hello All,

I am having a problem changing the following:

Below is an example of the formula in a cell, I have many of this cells in the worksheet.

=SUMIFS('[Daily Swaps 2017.xlsx]13 Jun 17'!$O$3:$O$1000,'[Daily Swaps 2017.xlsx]13 Jun 17'!$D$3:$D$1000,"AUD*",'[Daily Swaps 2017.xlsx]13 Jun 17'!$A$3:$A$1000,"S/B*")/COUNTIFS('[Daily Swaps 2017.xlsx]13 Jun 17'!$O$3:$O$1000,">0",'[Daily Swaps 2017.xlsx]13 Jun 17'!$D$3:$D$1000,"AUD*",'[Daily Swaps 2017.xlsx]13 Jun 17'!$A$3:$A$1000,"S/B*")

I wish to change all the 13 Jun 17 to another date such as 15 Jun 17 using vba.

I am using the following method:

For i = 1 To 20
For j = 1 To 20

Cells(i, j).Formula = Replace(Cells(i, j).Formula, "13 Jun 17", "16 Jul 18")

Next j
Next i

Using this method, I keep running into runtime error 1004.

However, when i try the following:

For i = 1 To 20
For j = 1 To 20

Cells(i, j).Formula = Replace(Cells(i, j).Formula, "1", "2")

Next j
Next i

All the 1 are replaced with 2.

Please Help. This does not work when i use "12" and "16" either (extra info)

Thank You!

[amarjeet.it]

can u plz upload the sample data to give better n precise solution in no time

[firetrue]

I have attached a picture, please advice if this is sufficient