.FormulaArray causes Run Time Error 1004, Unable to set the FormulaArray property

Hello,

In the Excel file, the formula array is

Please Login or Register  to view this content.

.

The macro that I've created is basically assigning the range, i.e $AG:$AG and/or Q:Q to $AG1:$AG5000 and/or Q1:Q5000 respectively.

Below is my macro and when check the result in the 'Immediate' Window, it's correct.

I even went and manually use the result from the 'Immediate' Window and paste it to the formula bar and Ctr + Shift + Enter and return no error.

Result from the 'Immediate' Window.

Please Login or Register  to view this content.

But for some reason, VBA just throw me an error.

May I know what's wrong with it.

Please Login or Register  to view this content.