VBA. populate array formula with VBA (glitch, problem)

Hi
I'm not sure where I'm going wrong.
The formula that I'm using is below:

Please Login or Register  to view this content.

I'm trying to populate this via VBA as below:

Please Login or Register  to view this content.

Formula is as Array formula (the usual Ctrl+Shift+Enter if you do it manually).
Quick brief of what the formula does.
The RED section below simply checks range AI4:AM4 to see if more than one cell is non-blank, if this is the case it will populate value "confl.", if this conditionn does not exist then the BLUE section checks the range AI4:AM4 and retrieves the value of firts non-blank cell.

Please Login or Register  to view this content.

Now when I'm trying to use VBA to populate this formula in range AO4:AO(last row as per column AH), it populates in a way that the row numbers are not dynamic and they are fixed for row 4.

e.g. instead populating:

Please Login or Register  to view this content.

It populates the formula with static row 4
e.g.

Please Login or Register  to view this content.

I have attached a spreadsheet (Sheet "Tracker") with the VBA in question together with with macro button. I have also added sheet "Manual example" that has the formula there to see it working properly. Note that on the code I have disabled the line that removes the formulas and keeps the vales. This is just you can see the mactro getting the formula wrong.



I would appreciate any help to get this right.
Cheers

If you enter an array formula into a range of cells, every cell contains the same formula (no change in cell references within the formula in any cell in the array of cells containing the formula). This is what your VBA code is doing. If you want to change the references dynamically, enter the formula in the first cell (AO4) only and then copy or filldown to the last cell (AO&lastrow).

Try something like this...

Please Login or Register  to view this content.

...as JoeMo suggests.

Thank you guys.
I was away for the weekend so its the first chance for me to reply.
JoeMo, thank you for your suggestion.
AlphaFrog, thank you for the code!

Code works exactly as I wanted. Now I can move forward again with mys macro. Cheers