Very creative Dale! Thanks I like it.

josh




"K Dales" wrote in message
...
As far as I know, FormulaArray will only return a set of values, not
formulas.

One thought (untested): perhaps you could adapt the "simple" way like
this:
1) Copy your range (myRange) to a new location (temporarily - you could
even
create a new sheet for this and delete the sheet when done) - I will call
this new range CopiedRange
2) Now try CopiedRange.FormulaR1C1 = myFormulaArray
3) Using CopiedRange.SpecialCells xlCellTypeFormulas you could find the
cells that now contain the formulas. Using a For Each you could loop
through
only these cells and copy them to the corresponding cells in the original
range.
4) Clean it up by deleting the temporary range and you should be done.

Like I said I haven't tested this to see if there are any unexpected
pitfalls, but it might be worth a try. It would save a whole bunch of
looping and presumably execution time.
--
- K Dales


"Josh Sale" wrote:

I'm looking for an efficient way to set the formulas on a range of cells.

My situation is that I have a VBA array that contains one formula in R1C1
format for each cell of the array. Some of the cells in the array
contain
empty strings indicating that the corresponding cell has no formula.

If is use code like:

myRange.FormulaR1C1 = myFormulaArray

Then the cells associated with elements of the array that contain empty
strings lose their value ... and I want to preserve the values in those
cells.

Right now, I'm using two nested loops to iterate through myFormulaArray
looking for non-empty strings and then setting the formula's a cell at a
time. It works but is slow as the array grows in size.

I've looked at the FormulaArray property and its description looks
tantalizingly like what I'm looking for. But it doesn't seem to work
either
.... but maybe I'm doing something wrong here since I don't really
understand
what a FormulaArray is.

Anybody got any suggestions?

TIA,

josh

p.s. Ideally the solution would work on XL97 and later ... but I could
live
with a solution that only worked with later versions of Excel.