CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2007
Greg Lovern
Greg Lovern

Reputation: 977

Setting FormulaArray to Formula skips every other row and column

A very fast way to enter formulas is like this:

Range("E5:H10").Formula = "=A1"

The column and row references adjust, so that for example the formula this code enters in cell H10 is "=D6".

However, that doesn't work for array formulas:

Range("E5:H10").FormulaArray = "=A1"

That puts the array formula {=A1} in each of the cells; no adjustments for rows or columns.

Of course you can loop through all the cells and enter a unique array formula in each one, but that isn't fast.

So I tried this:

Range("E5:H10").Formula = "=A1"
Range("E5:H10").FormulaArray = Range("E5:H10").Formula

But the results were surprising -- the references skip every other row and every other column; for example:

-- The formula in F5 is {=C1} instead of the expected {=B1}, and

-- The formula in D6 is {=A3} instead of the expected {=A2}, and

-- The formula in H10 is {=G11} instead of the expected {=D6}.

WTF?? Why would it skip rows and columns like that?? BTW I'm in Excel 2007 SP3.

For now I'm doing this, which was the fastest way I found that worked:

Range("E5").FormulaArray = "=A1"
Range("E5:H10").FillDown
Range("E5:H10").FillRight

That works but is probably slower than the direct way would be. So -- why does setting the FormulaArray property to the Formula property skip referenced rows and columns as shown above?

Upvotes: 0

Views: 1074

Answers (1)

Rory
Rory

Reputation: 34055

The A1 formula is evaluated relative to the top left cell of the range you are populating. So A1 works fine in E5, but B1 is up 4 and left 3 from E5, so the formula entered into F5 refers to the cell up 4 and left 3, which is C1. This repeats for the other cells. The simple solutions is to use:

Range("E5:H10").FormulaArray = Range("E5:H10").FormulaR1C1

since that formula is the same for every cell.

Upvotes: 0

Related Questions