Solved: How to re-apply named ranges

[xluser2007]

Hi All,

I have the following problem at hand.

I have a workbook with a handful of sheets. There is a tab called "input2" which contains many inputs for the required calculations.

In one of the output tabs (e.g."output1"), there is a reference to cells in the "input2" tab e.g. in output1 tab, cell A10 the formula is "=C15*input1!$E$73*(1+input2!E$14)*input2!E$28".

I have now renamed the cells in the "input2" tab as follows:

input1!$E$73 = sum_paymts
input2!E$14 = pays_int
input2!E$28 = pay_inf

These names however, don't automatically get reapplied.

How do I go about re-pplying all the named ranges across the workbook.

So that for example, the above formula would read as:

"=C15*sum_paymts*(1+pays_int)*pay_inf".

Any help appreciated.

(Note that the named ranges may be referenced as relative or absolute ranges i.e. we want input2!E$14 = input2!$E$14 = input2!$E14 = sum_paymts).

[xluser2007]

Is this easily possible, or is it an extremely difficult task (if so, please let me know) - it is definetely out of the grasp of my coding skills at this stage!

if I were to take a crack at it this would be the general idea:

1. For each name in the workbook, store the reference values/ formulas in an array.
2. For each cell in the workbook that has a formula, search for the above reference/ value formula.
3. if there is a successful search, then replace with the named range.

the tricky bit is working out the various combinations of absolute and relative references contained in the formulas that coincide with the named range (i.e. we want input2!E$14 = input2!$E$14 = input2!$E14 = sum_paymts as per before).

Any thoughts or ideas on this. I know that PUP utility unapplies names, but this is the reverse effect and was curious to know if its possible?

regards,

.

[Bob Phillips]

[vba]

Public Sub ChangeMyNames()
Const NAME_TO_APPLY As String = "MyName"
Const CELL_REF As String = "E1" 'can be any absolute/relative combination
Dim sh As Worksheet

On Error Resume Next
ActiveSheet.Cells.ApplyNames Names:=NAME_TO_APPLY, _
IgnoreRelativeAbsolute:=True, _
UseRowColumnNames:=True, _
omitColumn:=True, _
OmitRow:=True, _
Order:=1, _
AppendLast:=False
On Error GoTo 0

For Each sh In ActiveWorkbook.Worksheets

ApplyName ActiveSheet.Name, sh, CELL_REF, NAME_TO_APPLY
Next sh
End Sub

Public Function ApplyName(ByVal SourceSheet As String, _
ByRef TargetSheet As Worksheet, _
ByVal CellRef As String, _
ByVal NewName As String)
Dim mpColLetter As String
Dim mpRowNum As Long
Dim mpSource As String
Dim mpCellRef As String

mpColLetter = ColumnLetter(TargetSheet.Range(CellRef).Column)
mpRowNum = TargetSheet.Range(CellRef).Row
If InStr(mpSource, " ") > 0 Then

mpSource = "'" & mpSource & "'! "
Else
mpSource = SourceSheet & "!"
End If

With TargetSheet

mpCellRef = mpSource & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart
End With

End Function

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]

[JimmyTheHand]

Just a thought. Named range and formula could be compared e.g. this way.

[vba]Dim N As String, F As String
N = Replace(Mid(Names("Name1").RefersTo, 2),"$","")
F = Replace(cel.Formula, "$", "")
If InStr(F, N) > 0 Then
cel.Formula = Replace(F, N, Name1)
End If
[/vba]
HTH
Jimmy

[xluser2007]

Hi Bob/ Jimmy,

Thank you so much for your kind efforts. I'm away from my work computer at the moment and can't test this out right now, but will the first thing tomorrow morning.

Bob, every time you send me something to look at, it takes me a week to get my head around the technique, and then realise why it is porbably the most robust way of doing it! Thanks for your help and patience with me.

Jimmy, I've been reading your posts with keen interest of late, thanks for replying to this problem, I'll test and let you know.

Cheers,

[xluser2007]

Bob,

just tried running your code now.

Couldn't quite get it to work i.e. the redefined names weren't reapplied on a test workbook (as attached).

-> More specifically, In the attached workbook, Sheet2 ranges "B2", "C5" and "E3" didn't change formula references to be "namea1", and Sheet3 ranges "B4", "C8" and "E2" didn't change formula references to be "namea2".

Could you please explain anything I'm doing incorrectly?

regards

[xluser2007]

Bob,

Just re-examining your code again, in order to replace all the newly defined names in the Activeworkbook throughout the workbook, is there a second loop required to cycle through all the names?

For example, there is the constant defined as "NAME_TO_APPLY", do we need to manually update this in the code, or should we loop through all names and set them to be equal the NAME_TO_APPLY constant.

Also could you please explain the purpose of the "CELL_REF" constant, the coding has me confused.

Jimmy,

with your code, I see what you are trying to do: First substituting out all the absolute "$" references in the names and then doing the same for any addresses in cells with a formula, and then replace the names that are in each formula.

Could you please explain how to cycle through all cells witha formula and how to cycle through all names that could be in each cell?

Appreciate all your help with this one guys.

[Bob Phillips]

I have just tried it and it works fine ... but I did adjust.

The constant NAME_TO_APPLY is the base name that you wish to apply, namea1 or nameb2. CELL_REF is the cell address that that name refersto to, A1 or B2, just the cell not the sheet name. As I said in my comments, you can set CELL_REF to A1, $A1, A$1, or $A$1, they will all work.

The ChangeMyNames procedure was just to show how to call the real grunt function, what you want is

[vba]

For Each sh In ActiveWorkbook.Worksheets

ApplyName ActiveSheet.Name, sh, "A1", "namea1"
ApplyName ActiveSheet.Name, sh, "B2", "nameb2"
Next sh
[/vba]

I suppose I could extract the cell reference from the name, I might take a shot at that later today.

[xluser2007]

Bob, Thanks, I now starting to appreciate fully what your code is doing .

Two queries:

Firstly,

Is the Activesheet necessary, or can we just replace with "sh" i.e. the sheet that you are looping through, i.e.

[vba]'replace
ApplyName ActiveSheet.Name, sh, "A1", "namea1"
'with
ApplyName sh.Name, sh, "A1", "namea1"[/vba]
It seems to work this way.

Secondly,

In your code posted you had the following:
[vba] On Error Resume Next
ActiveSheet.Cells.ApplyNames Names:=NAME_TO_APPLY, _
IgnoreRelativeAbsolute:=True, _
UseRowColumnNames:=True, _
omitColumn:=True, _
OmitRow:=True, _
Order:=1, _
AppendLast:=False [/vba]
Were you trying to illustrate an aletrnative way of achieving the same result? That is using the ApplyNames method as shown here?

If so, I tried to do the following:

[vba]Option Explicit

Public Sub ChangeMyNames()

Dim oneName As Name


For Each oneName In ThisWorkbook.Names
ActiveSheet.Cells.ApplyNames Names:=oneName.Name, _
IgnoreRelativeAbsolute:=True, _
UseRowColumnNames:=True, _
omitColumn:=True, _
OmitRow:=True, _
Order:=1, _
AppendLast:=False
Next oneName

End Sub[/vba]
But it did not like the "ActiveSheet.Cells.ApplyNames..." and gave the error "Run time error 1004: MS Excel cannot find any references to replace"

I ran this on Sheet2 (the Activesheet).

Could you please explain what is wrong with this approach, and also how to loop through all sheets and achieve the same purpose?

Please note I am still really keen to see how to generalise your method by automatically extracting the cell reference from the name. You have already given up heaps of your time on this, if you do get to do this that would be awesome to learn from.

[Bob Phillips]

I used activesheet because I have found through the fire of experience that it is always best to precede a range name with the name of the sheet that it refers to. Sometimes it works without, but often it doesn't. I have NEVER found that it doesn't work with.

One thing that I forgot to mention is that when you run it you should be on the sheet that the name refers to (see lsater for the why).

No, I wasn't being cute in showing two ways to do the same thing. ApplyNames would be the preferred way to do it, but ApplyNames only works on the sheet that the name refersto, so I cannot use it on the other sheets. I could have dispensed with ApplyNames altogether, but I would have to test for activesheet and do a slightly different replacement, as referring cells on the same sheet as the name are not prefixed by the sheet name (i.e. =A1, not =Sheet1!A1). By allowing the loop through the worksheets to even process the activesheet is not a problem, it just finds nothing in that case.

[xluser2007]

Bob,

I see what you are trying to do. I tested it for my purposes and it did work really brilliantly, as usual with your codes . Thank you

I have many questions on generalising the problem to automatically determine the relevant names and their references, instead of manually entering them (this workbook that I was cleaning had about 25 named ranges, others may have more), so really keen to develop on this further with you.

But before doing that I wanted to also try Jimmy's approach to the problem (it seems like a very interesting alternative). In his approach, it is essentially trying to compare a relative named reference with relative formula reference and substitute with the named range name where there is a successful match.

Jimmy/ Bob, I was wondering how you would loop through all cells with formulas and replace all possible named range occurrences in the formulas using this approach. Your insights (as always) would be appreciated.

sincere regards,

[Bob Phillips]

I think my approach would work if you did each name iteratively, asd it uses the builtin replace function.

I still have to do some more work on this, but I am off on a bike ride now, so I will return later.

[Bob Phillips]

Or do you also mean, automatically grab the names and replace them, no EU direction?

[xluser2007]

Or do you also mean, automatically grab the names and replace them, no EU direction?

Yeah, this is more what I was thinking. Automatically ensuring that all names are replaced across all sheets, so no manual entry of names is necessary.

I'm a bit unclear on what "EU" direction means though, could you please clarify?

I was thinking with your approach, you had mentioned that you need to be on the Sheet which contains the relevant named references that you want to replace across the workbook.

Is it fair to say that if we just loop your entire code through all worksheets with named references (so all possible newly defined names are definitely replaced), would this solve the issue restricting ourselves to be on the relevant worksheet with the neamed range references when we run the macro?

I hope this clarifies what I meant. Will talk to you soon, have a great bike ride .

regards,

[Bob Phillips]

I'm a bit unclear on what "EU" direction means though, could you please clarify?

I mean that the code would not have to get any direction from the user/operator as to what names to replace, it would determine that.

Is it fair to say that if we just loop your entire code through all worksheets with named references (so all possible newly defined names are definitely replaced), would this solve the issue restricting ourselves to be on the relevant worksheet with the neamed range references when we run the macro?

If I am understanding you, that would not work, because a name is not just necessarily referenced on the sheet it applies to.

[Bob Phillips]

Here's my first shot at automatically applying all names. STill needs some more work, but in the interim ...

[vba]

Public Sub ChangeMyNames()
Dim sh As Worksheet
Dim SheetName As String
Dim CellRef As String
Dim nme As Name

For Each nme In ActiveWorkbook.Names

If Not nme.Name Like "*_FilterDatabase" And _
Not nme.Name Like "*Print_Area" And _
Not nme.Name Like "*Print_Titles" And _
Not nme.Name Like "*wvu.*" And _
Not nme.Name Like "*wrn.*" And _
Not nme.Name Like "*!Criteria" Then

CellRef = Range(Application.Evaluate(nme.RefersTo)).Address
For Each sh In ActiveWorkbook.Worksheets

SheetName = Replace(Left$(nme.RefersTo, InStr(nme.RefersTo, "!") - 1), "=", "")
If SheetName = sh.Name Then

On Error Resume Next
ActiveSheet.Cells.ApplyNames Names:=nme.Name
On Error GoTo 0
Else

ApplyName SheetName, sh, CellRef, nme.Name
End If
Next sh
End If
Next nme
End Sub

Public Function ApplyName(ByVal SourceSheet As String, _
ByRef TargetSheet As Worksheet, _
ByVal CellRef As String, _
ByVal NewName As String)
Dim mpColLetter As String
Dim mpRowNum As Long
Dim mpSource As String
Dim mpCellRef As String

mpColLetter = ColumnLetter(TargetSheet.Range(CellRef).Column)
mpRowNum = TargetSheet.Range(CellRef).Row
If InStr(mpSource, " ") > 0 Then

mpSource = "'" & mpSource & "'! "
Else
mpSource = SourceSheet & "!"
End If

With TargetSheet

mpCellRef = mpSource & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart
End With

End Function

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]

[xluser2007]

Bob,

Sorry for my delayed reply, this is the first time I;ve had this weekend to sit down and run through your code.

I've tested it has worked simply brilliantly .

I just made one small change (as I kept getting an error):

From:

[vba]CellRef = Range(Application.Evaluate(nme.RefersTo)).Address[/vba]
To:

[vba]CellRef = Range(nme.RefersTo).Address[/vba]
It was really cool to see that you had removed the replaciment of names like Print Area, I didn't realise that when you set a print area it does it automatically as a named range. I'm not sure though, how you came up with the otehr wildcard exclusions like "*wvu.*" And "*wrn.*" etc, could you please clarify?

Also It is interesting to see that you use the inbuilt VBA applynames object, for sheets where the named range reference matches the sheet name and your custom made macro for all others, very nifty indeed. I noticed for the applynames usage you did not use all the features as per before:


[vba]For Each oneName In ThisWorkbook.Names
ActiveSheet.Cells.ApplyNames Names:=oneName.Name, _
IgnoreRelativeAbsolute:=True, _
UseRowColumnNames:=True, _
omitColumn:=True, _
OmitRow:=True, _
Order:=1, _
AppendLast:=False
Next oneName [/vba]



I still don't have my head around your custommade "Applyname" macro, but one step at a time for me.

As for testing I have tried running it on the workbook attached by mixing the formulas around with multiple named range combinations and it replaced just fine.

I also tried starting with by varying the activesheet away from where the names are defined e.g. on Sheet2 or Sheet3, rather than Sheet1 (where the names were defined) and it worked just fine.

Here's my first shot at automatically applying all names. STill needs some more work, but in the interim ...

Given the above, could you please clarify, which part(s) needs more work, the above code runs quite nicely, I'm keen to understand where the imporvements would come from.

sincere thanks and regards for your time and efforts, appreciate it.

[Bob Phillips]

There are two things that I need to do.

I need to cater for name constants, e.g. a name of VAT with a value of 17.5%. The code that you have seen crashes on that, but I have code to cater for it already.

The other, and much trickier problem, concerns local names against global names. All the names that you defined in your example workbook are global, so you can happily just replace the address with the name. But, you can also define a local name, such as Sheet2!myName referring to Sheet2!E5. If you try to refer to a local name from anywhere other than Sheet2 you get an error, unless you alos precede it with the sheet name. That is one aspect, but you can have the same local name for more than one sheet, pre-pended by the sheet name uniquifies them, or evn have a global name and a local name. The tricky part is knowing when a name also has a local version for the sheet that you are processing. I have done this once before, I just need to dig the code out and apply.

I am surprised that you were getting an error, I got the error with the way you changed it?

As to those system names, I have a list in a library. I found those many years ago and just stored them away. There are probably more by now, especially with 2007.

And I removed all of the extra argument/parameters from ApplyNames as I was just using the defaults, and removing them clarifies the code's intent.

[Bob Phillips]

Actually, I think I was over-egging the problem, I was intellectualising it from the persepctive of identifying usage of local and global names in a workbook. This is different in that we have cells TO BE REPLACED BY local or global names, and as it turns out, that is a darn sight simpler.

If you have a cell referring to a cell on another sheet that has a local name attached to it, the code already addresses that. What it doesn't address is cells on the sheet that the local name is also on pointing to the cell that the local name is attached to. An extra call to my ApplyName procedure, with suitably adjustred parameters, takes care of that.

[vba]

Public Sub ChangeMyNames()
Dim sh As Worksheet
Dim SheetName As String
Dim CellRef As String
Dim RefersToRange As Range
Dim nme As Name

For Each nme In ActiveWorkbook.Names

If Not nme.Name Like "*_FilterDatabase" And _
Not nme.Name Like "*Print_Area" And _
Not nme.Name Like "*Print_Titles" And _
Not nme.Name Like "*wvu.*" And _
Not nme.Name Like "*wrn.*" And _
Not nme.Name Like "*!Criteria" Then

Set RefersToRange = Nothing
On Error Resume Next
Set RefersToRange = nme.RefersToRange
On Error GoTo 0
If Not RefersToRange Is Nothing Then

CellRef = Range(nme.RefersTo).Address
For Each sh In ActiveWorkbook.Worksheets

SheetName = Replace(Left$(nme.RefersTo, InStr(nme.RefersTo, "!") - 1), "=", "")
If SheetName = sh.Name Then

On Error Resume Next
ActiveSheet.Cells.ApplyNames Names:=nme.Name
On Error GoTo 0
Else

ApplyName SheetName, sh, CellRef, nme.Name
End If
Next sh
If InStr(nme.Name, "!") > 0 Then

SheetName = Left$(nme.Name, InStr(nme.Name, "!") - 1)
Set sh = ActiveWorkbook.Worksheets(SheetName)
ApplyName SheetName, _
sh, _
Replace(Range(nme.RefersTo).Address, SheetName & "!", ""), _
Replace(nme.Name, SheetName & "!", "")
End If
End If
End If
Next nme
End Sub

Public Function ApplyName(ByVal SourceSheet As String, _
ByRef TargetSheet As Worksheet, _
ByVal CellRef As String, _
ByVal NewName As String)
Dim mpColLetter As String
Dim mpRowNum As Long
Dim mpSource As String
Dim mpCellRef As String

mpColLetter = ColumnLetter(TargetSheet.Range(CellRef).Column)
mpRowNum = TargetSheet.Range(CellRef).Row
If InStr(mpSource, " ") > 0 Then

mpSource = "'" & mpSource & "'! "
Else
mpSource = SourceSheet & "!"
End If

With TargetSheet

mpCellRef = mpSource & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart

mpCellRef = mpSource & "$" & mpColLetter & "$" & mpRowNum
.Cells.Replace What:=mpCellRef, Replacement:=NewName, LookAt:=xlPart
End With

End Function

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]

[xluser2007]

Bob,

That code works really nicely, just tested it.

I have a query though (as always ).

I tried setting a named range constant "const" = 10.

then in a Sheet2 G6, i Tries I tried =Sheet1!B2*$D$6*Sheet1!A1*10

which gave "=nameb2*$D$6*namea1*10", not =nameb2*$D$6*namea1*const".

I only mention this because you explained previously that:

I need to cater for name constants, e.g. a name of VAT with a value of 17.5%. The code that you have seen crashes on that, but I have code to cater for it already.

I'm not sure whether this feature is really required, constants entered directly as a named range would be too volatile to control for especially when we just apply them by checking they are contained in a formula i.e. a formula may contain =C1*10, but the 10 may not be realted to a named range constant of value 10 necessarily, the formula may change to =C1*100.5 i.e. const was not related to the formula and thus should not be replaced as such.

In a nutshell, I feel your code above as it works for the above example is excellent and the feature of applying named constants may led to issues.

I'm keen to hear your thoughts on this, in case I have stated something incorrectly.

Aside from the above discussion point, I think this is really elegant coding ( I imagined the absolute relative checking of a formula for a named range to be a tedious exercise), you managed to do it in 10 lines - nice one .

Thanks again for helping me understnd and develop skills in VBA, really appreciate it mate.

Cheers,