Solved: macro to change NAMED formulas

[xluser2007]

Hi All,

I have a bit of challenge that I'm trying to solve, need expert guidance.

Basically here is the scenario (simplified).

We have 7 spreadhseets for companies:

ABC.xls, XYZ.xls, GFD.xls (names are randomly chosen for explanation).

They are all structurally identical (i.e. each is taken from the same template, only values are different).

There is Total_[month].xls spreadsheet which is taken from the same template as the agents, but is simply a sum of all the agents for certain tabs e.g. Total_200710.xls is the Total for October 2007 total spreadsheet.

Now say the "Data" tab, cell A1 in Total_200710.xls is a direct one-to-one (as they come from the same structured templates) sum from the Agent spreadsheets i.e:

Total.xls_200710 Data A1 = (ABC.xls "Data" _200710 A1) +(ABC.xls "Data" _200710 A1) + ... (GFD.xls "Data" _200710 A1)

Where the formula is named ABC_Data (=ABC.xls "Data" _200710 A1).

The Agent names are really long and the formulas break down when you do a direct sum like this (goes over character limit). So we named the formulas (to preserve one-to-one range refs between the templates and make the formulas shorter and thus work!).

Basic issue is: When we copy over 200710 files into 200711 (Nov-07) folder, the Total.xls still references ABC.xls "Data" _200710 A1 etc, not ABC.xls "Data" _200711 A1.

There are a lot of names (about 50) and changing them one by one every month would be really time consuming.

is there a way to open up Total_[month].xls and if the named formulas contain the previous month, then change it to the current [month]? i.e. if current month is Nov-07 and the formuals 200710, then just change the 200711 in teh folder path reference and everything will be good.

The names stay the same, just need to change the date reference in the formulas if they contain the previous one.

Any help on this would be very greatly appreciated (the above logic is all that it should take, but is hard for a VBnewbie to code)

[xluser2007]

Ok, so I've got the following set up in a new spreadsheet (Starting A1):

Name new Formula
ABC_Data '='C:\200711\ABC\Spreads\[ABClongername.xls]Data'!A1
GFD_Data '='C:\200711\GFD\Spreads\[ABClongername.xls]Data'!A1
etc

So all I need to do in Total_200711.xls is:

(Because they are named formualas they are defined relative to a cell).

For All Data Formulas Got to A1 in ALL Data tabs for ALL companies and Total_200711.xls then for each name set the formual in column B.

How do I do this (Setting the name pasrt to the formula)?

Any help, guru guidance required (:banghead:).

[mikerickson]

Perhaps something like this.
Sub ChangeNames()
Dim oneName As Name
For Each oneName In ThisWorkbook.Names
With oneName
ThisWorkbook.Names.Add Name:=.Name, RefersTo:=NewRefersTo(.RefersTo)
End With
Next oneName
End Sub

Function NewRefersTo(oldRefersTo) As String
NewRefersTo = Application.Substitute(oldRefersTo, "Workbook200701", "Workbook200801")
End Function

If you post some examples of old RefersTo vs. new RefersTo the UDF NewRefersTo could be refined.

[xluser2007]

mikerickson, take a bow.

That worked beautifully, because as desired it went through and changed the relevant dates in the named formulae.

only critical step was to make sure that I went to A1 in all the worksheets of all the opened workbooks that I was sourcing from (as the named formulae are relative references).

I can't thank you enough, saved me a lot of hair pulling. I was trying to work out how to got through each and every name and re-update the formulas indivivuaally. I didn't realise a simple substitution affects formulas like this.

Just FYI, for my syntax, the folders changes as follows:

C:\Documents\200709\ABC.xls for September 2007

Changes to

C:\Documents\200710\ABC.xls for October 2007. So the UDF syntax is just tweaked as follows:

Function NewRefersTo(oldRefersTo) As String
NewRefersTo = Application.Substitute(oldRefersTo, "\200709\", "\200710\")
End Function
if I have further queries (or developments) I'll kepp you posted.

Thanks again.

[mikerickson]

This change should allow any cell to be the ActiveCell. Selecting A1 should not be needed.
With oneName
ThisWorkbook.Names.Add Name:=.Name, RefersToR1C1:=NewRefersTo(.RefersToR1C1)
End With

[xluser2007]

Mikerickson thanks again, I'll test this on my terminal tomorrow.

Could you please explain to me the logic (if you have time) of the line presented.

the way its structured and syntax are confusing me slightly, namely:

ThisWorkbook.Names.Add Name:=.Name, RefersToR1C1:=NewRefersTo(.RefersToR1C1)

is the .RefersTo standard VBA syntax? How does the NewRefersTo(.RefersToR1C1) line work exactly?

regards,

[mikerickson]

.RefersToR1C1 is a string, typicaly "=[Workbook1.xls]Sheet1!R1C1"

NewRefersTo is a function that substitutes Workbook2 for Workbook1 in a string, so in the example

NewRefersTo(.RefersToR1C1) "=[Workbook2.xls]Sheet1!R1C1"

[xluser2007]

Worked wonderfully, appreciate your help mikerickson!:hi:

[xluser2007]

Hi All,

Just kept running into the following issue with the macro when running (pls see screenshot).

This macro has worked and changed the folder references correctly for 4 workbooks.

When correcting for a fifth workbook (In the same folder), It fails only for correcting half the formulas (for one workbook only), and the other half of formulas it changes properly i.e. comes up with the error message and doesn't change formula refs for half the formuals.Although it has the above error for one workbook only, there is nothing fundamentally different about this book to the other books it works correctly for.

Could anyone kindly suggest any ideas as to what may be the issue and how to correct this?

regards

[xluser2007]

Sorry, forgot to note that the error came through in the following line:

ThisWorkbook.Names.Add Name:=.Name, RefersToR1C1:=NewRefersTo(.RefersToR1C1)

[xluser2007]

Ahh after much testing I realised the issue was an old, unused name with an error reference that was causing the macro to crash (amongst 50 names it was hard to pick it as the VBA errors aren't that helpful).

Just an FYI in case anyone else runs into the same issue.

In order to correct the macro what is teh best way to amend it for this error?

[mikerickson]

Sub ChangeNames()
Dim oneName As Name, oldRef As String
For Each oneName In ThisWorkbook.Names
With oneName
If Application.ReferenceStyle = xlA1 Then
oldRef = .RefersTo
Else
oldRef = .RefersToR1C1
End If
If IsError(Evaluate(oldRef)) Then
If MsgBox("Name " & .Name & " currently evalutes to an error." & vbCrLf & "Delete name?", vbYesNo + vbDefaultButton2) = vbYes Then
oneName.Delete
End If
Else
ThisWorkbook.Names.Add Name:=.Name, RefersToR1C1:=NewRefersTo(.RefersToR1C1)
End If
End With
Next oneName
End Sub

[xluser2007]

Hi Mikerickson, thanks for that, worked really well.

Thank you for your help throughout this problem. As a VBnewbie, I'm finding debugging to be time-consuming but am getting a lot out of it and through asking questions in this awesome and helpful forum.

Many thanks also to the other helpful VBAX Guru volunteers :friends:.

regards

[mikerickson]

Glad to have helped.

Debugging is my favorite place to learn.

Situations like this remind one that "users do the darndest things".