Relative References in Array Formulas - VBA

Hi all,

I am trying to figure out why something is not working out for me.

I am using the following code to insert an array formula within the specified range.

It works, except the the last A1 (which I have left as a relative reference) is not actually changing as you go down the rows...(i.e. the formula is exactly the same in the whole range)... I would expect it to change to A2, A3, A4, etc....

Please Login or Register  to view this content.

with this folllowing code which is not an array formula, it works as expected....is it something to do with array formulas... do I need to reference the cells differently?

Please Login or Register  to view this content.

EDIT:

Is it perhaps because I am starting this formula in row 3 and since it references A1, it is confused somehow?

Hi Richard,

Once again, you have solved my dilemma....Thank you very much!

Just curious.... was I right in assuming it is because I started the formula in Row 3 and since I was referencing A1 (ie. Row 1) , that caused VBA to freeze it?

Originally Posted by RichardSchollar

No, I don't think that makes any difference - I think it is simply a product of the inner workings of the FormulaArray property. Could be wrong, however

Richard

.... I guess just one more reason why VBA can be so confusing....

Well, thanks once again Richard. I always appreciate your valuable help and advice.

Have a good day!