VBA Change "Cell Reference In Formulas" To "Range Names In Excel" for a whole workbook

[actjfc]

Excel friends,

Let's say wherever this vlookup(?,$B5:$C25,2,0) shows up in a Workbook. If I have defined a name for all ranges, and $B5:$C25 is Prices, then the VBA code should replace the vlookup to show: vlookup(?,Prices,2,0). I would like to find a VBA code to perform this kind of replacement in a whole workbook:

The macro should perform just as explained here:
https://www.extendoffice.com/documents/excel/846-excel-change-cell-reference-to-name.html

Can somebody help to develop it or point out where to find open source code?

Thanks!

 

[alansidman]

vlookup(?,Range("Prices"),2,0)

additional information at https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges

 

[Peter_SSs]

Perhaps you could tell us a bit more about your circumstances that mean you couldn't just manually follow what is described in the link you provided. Since you can select all the names and apply them simultaneously, what is it about your circumstances that precludes that? Is it just that you have multiple worksheets in your workbook, or something else?

 

[actjfc]

I am sorry for my delayed response. Labor Day delay! Yes, I have multiple workbooks and about 1500 references. I feel more in charge if I know and see the VBA code. I will take a look at thespreadsheetguru code, and see how I can make a loop to make it work. I plan first to identify all references used in the workbook generating a two column list, and them apply the macro to create the names. Also, some of them may be local and other global. The regular Excel way to do it runs behind scenes, I like to own the code. If anybody has already something is highly appreciated. Thanks!