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

actjfc

Active Member
Joined
Jun 28, 2003
Messages
409
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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,419
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 28, 2003
Messages
409
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,395
Messages
5,444,225
Members
405,274
Latest member
go2nivas

This Week's Hot Topics

Top