asked on

Excel macro to update formulas in a spreadsheet

I need a macro to look at the formulas in Col C of my spreadsheet and where ever it finds a “year \month-year” reference in the formula it must change it as appropriate. Let me explain further:

Example 1:In cell C4 there is this formula: =SUM('G:\INTERNAL REPORTING - POST ACQUISITION\GTCS\Executive Summary\2014\1214\[BV - 1405020 - 100.xlsx]Sheet1'!$B$21:$B$44)/1000

When I run the macro it must change the formula to:

=SUM('G:\INTERNAL REPORTING - POST ACQUISITION\GTCS\Executive Summary\2015\0115\[BV - 1405020 - 100.xlsx]Sheet1'!$B$21:$B$44)/1000

Example 2: If the macro finds this formula:
=SUM('G:\INTERNAL REPORTING - POST ACQUISITION\GTCS\Executive Summary\2015\0115\[BV - 1405020 - 100.xlsx]Sheet1'!$B$21:$B$44)/1000

It must change it to:

=SUM('G:\INTERNAL REPORTING - POST ACQUISITION\GTCS\Executive Summary\2015\0215\[BV - 1405020 - 100.xlsx]Sheet1'!$B$21:$B$44)/1000

If the formula has no date in it the macro must leave it alone.
Demo-Version.xlsx

Saurabh Singh Teotia

You can run the following code which will do what you are looking for...

Sub checkformulas()
    Dim rng As Range, cell As Range


    Set rng = Cells.SpecialCells(xlCellTypeFormulas)

    For Each cell In rng


        If InStr(1, cell.Formula, "\2014\1214", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "\2014\1214", "\2015\0115")
        ElseIf InStr(1, cell.Formula, "\2015\0115", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "\2015\0115", "\2015\0215")
        End If

    Next cell
End Sub

Open in new window

Please be cognizant of the fact that the first time you the macro it will update dates of 2014\1214 to 2015\0115..However when you the same macro again it will again change those dates from \2015\0115 to \2015\0215

Saurabh...

ParaGlow

ASKER

Thanks for your help. I will test out and let you know.

ParaGlow

ASKER

I haven't yet had a chance to test your code but I was wondering whether it only handles just 2 specific situations. Or whether it is dynamic.

Saurabh Singh Teotia

These lines in the code which you see-->

If InStr(1, cell.Formula, "\2014\1214", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "\2014\1214", "\2015\0115")
        ElseIf InStr(1, cell.Formula, "\2015\0115", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "\2015\0115", "\2015\0215")

Open in new window

You can update it basis of whatever you want to replace and it will do what you are looking for..

Saurabh..

ParaGlow

ASKER

ok. That should work. It would be better if the code could just read what's there and increment to the next month, or the next year and month, as the case might be.

Saurabh Singh Teotia

Sure, Once you change that or update this will do what you are looking for..

ParaGlow

ASKER

Not quite what I was looking for. Each month I would have to manually make changes to the code before running it.

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial