Hello,

I inherited an Excel monster with lots of stale code, recorded macros,
goto-statements (!), you know what I mean. A tumor that has grown and
festered over many years.

So far I've been able to clean up a lot, and speed it up at the same
time. But there is one thing where I get stuck.

Let's say column A contains the following strings:
£='[##]worksheet_name'!$E$4
£='[##]worksheet_name'!$E$5
£='[##]worksheet_name'!$E$6
£='[##]worksheet_name'!$E$7
...
Actually, these are not hardcoded strings but values of an =INDIRECT()
formula. Don't ask, yer head will asplode. :-)

B, C and D are blank except for the first row, which contains the name
of other .xls files, who are always in the same directory as the main
file. Column A functions as a template for columns B, C and D.

The VBA code has to generate the formulae for B, C and D. How did they
do that?

1. copy column A4:A200 to B4:B200 (range(A).select, selection.copy,
range(B).select, selection.paste)
2. in column B, search & destroy^Wreplace every occurence of [##] with
[fileB.xls] (using the replace method on the range object). fileB.xls
is in the top row of column B.
3. in column B, search & replace every occurence of £= with = (again
using the replace method on the range object)
Repeat for column C and D.

3. is actually quite clever, because before the pasted text was just
text, but by removing the £ at the front of the text, Excel evaluates
it as a function.
But in 2. something goes wrong when I changed it from the short
filename to the full filename with ActiveWorkbook.Path: part of the
filename was repeated inside the formula. Looked really buggy...

My fix was to read in column A from range to variant array, loop
trough the array, make the text replacements in the formula template
string, and set the cell.Formula for each individual cell in column B.
My workaround works 100% correct, I'm confident about that. But...
sloooow. Of course, I have to iterate trough every cell in column B to
set the formula. If it were just a value that I had to write, it would
be a simple matter of saying colB=varArray. Bada bing, bada boom! No
such luck.

I have done some research, but I didn't find a way to set the formulae
of an entire range from an array. It seems like it can only be done
with values. Is that true?

Worst case scenario would be that I would have to restore the copy/
paste code and try to fix it, not rewrite it... :(

--
Amedee