Named Range to Cell References

[smitpau]

Hi,

I'm trying to convert around 1000 named ranges to cell references.

I found this method on Reddit below which works until after changing the formulas from = to start with |
The problem is I can't find and replace the | back to = as it's not a formula anymore.

Don't know if there is a way to do this as I believe the logic to replace the = sign is correct to avoid recalculation which will take a long time for each change.


Thanks,

https://www.reddit.com/r/excel/comments/5jfri8

Method:

List out all names and associated ranges with the following macro, it creates a list of names and range from your active cell (alt+F11 - copy to ThisWookbook and run)

Sub listnamesranges()
Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
For r = 1 To nms.Count
On error resume next
wks.Cells(r, 1).Value = nms(r).Name
'wks.Cells(r, 2).Value = nms(r).RefersToRange.Address
wks.Cells(r, 2).Value = "''" & nms(r).RefersToRange.Parent.Name & "'!" & nms(r).RefersToRange.Address
Next
End Sub

Then disable all formulas thus: ctrl+H = to |

Select the first name in the new table of ranges and run this macro

Sub changereferences()
Do Until ActiveCell.Value = ""
rWhat = ActiveCell.Value
rTo = ActiveCell.Offset(0, 1).Value
Cells.Replace What:=rWhat, Replacement:=rTo, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Then enable all formulas thus: ctrl+H | to = (my issue!)

I transformed 50,000 formulas in a split second without issue..

Re-enabling the formulas may take a while depending on complexity.

 

[smitpau]

No problem seems to be working now!