Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Convert indirect function to direct links
I am looking for a visual basic macro that would do the following:
Convert an indirect function into a direct link. I have a variety of indirect functions in a file that link to external files, and as the indirect function is volatile, these fields "#ref" out when the files are closed. Rather than redo all of these to be direct links, I would like a macro that goes through and converts the indirects to direct links. Since all the information is on the sheet and in the formula, it seems like it should be straightforward, but is beyong my simple VBA programming abilities. For greater clarity, I am using the following types of indirect functions: SUM(INDIRECT("'"&E$8&E$9&"'"&"!"&E$10&$A18&":"&E$1 1&$A18)) Where E$8 is filename (e.g. "File") E$9 is the sheet name (e.g. "Sheet1") E$10 is the beginning column (e.g. "D") E$11 is the ending column (e.g. "F") $A18 is the row (e.g. 10) So the macro would convert the above macro to: SUM('[File]Sheet1'!$D$10:$F$10) I would like this macro to be made generic so it applies to different indirect functions (e.g. some are sums, some are single links), and to be set up so I can run it for all indirects that are currently selected (e.g. I highlight a range of cells, hit the macro shortcut key, and all indirects in those cells are converted to direct links, and cells with other formulas are not affected. I put this in as a google answers post too, if anyone wants the money! http://answers.google.com/answers/threadview?id=711588 Thanks in advance PC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating links specified with INDIRECT | Excel Discussion (Misc queries) | |||
Indirect links | Excel Discussion (Misc queries) | |||
Error using Excel add-in with INDIRECT.EXT function | Excel Discussion (Misc queries) | |||
Using Indirect en direct cell references | Excel Discussion (Misc queries) | |||
Convert Excel Functions to VBA Macro Function | Excel Programming |