Consider the following algorithm:

Create a VBA array, say OrigArray, that contains the values of the
myRange. This is a single statement along the lines of

dim OrigArray
OrigArray=myRange.values

[though there might be a limit of how many elements XL97 supports.]

Now, loop through MyFormulaArray and for each element that should
remain unchanged replace it with the corresponding element in
OrigArray.

Finally, transfer MyFormulaArray to myRange as you are doing right now.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , jsale@tril says...
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.