VA help to enter array formula

[asad]

Hello All,

I am trying to enter following array formula using VBA. But it does not change the lookup cell. Can someone please help me out?

Range("C6:C29").Formula = "=ROUND($J$1*SUM(IF($P$6:$P$293=[B]A6[/B],[COLOR=#FF0000][/COLOR]IF(MONTH($Q$6:$Q$293)=MONTH($E$1),$R$6:$R$293))),0)"

"A6" in the above formula need to change to A7, A8 and so on.

Any help is appreciated.

Asad

 

[Trebor76]

Hi asad,

I can vaguely remember trying to make a dynamic range with array formulas and in the end just restored to do doing it cell-by-cell which as you only have 24 isn't an issue in this case:

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 6 To 29
        Range("C" & lngMyRow).FormulaArray = "=ROUND($J$1*SUM(IF($P$6:$P$293=A" & lngMyRow & ",IF(MONTH($Q$6:$Q$293)=MONTH($E$1),$R$6:$R$293))),0)"
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

I'd be glad to know if there's a more efficient way

Regards,

Robert

 

[asad]

Thanks for that Robert.
I will test it tomorrow and get back to you.

 

[Marcelo Branco]

In VBA to enter an array formula in multiple cells is tricky.
See topic 3.2 in this excellent article by Colin Legg
Working With Range.FormulaArray In VBA | RAD Excel

M.

 

[asad]

I am unable to open the link sent by Marcelo.
Is there another way to check it out?

 

[Jonmo1]

That link basically shows 2 methods to do what you want.

1) Loop every cells you want to enter the formula in, incrimenting the reference A6 to A7 to A8 as it goes.
This is the method shown by Trebor

2) Enter the formula in only the first cell, then Fill it Down
For this method, try

Range("C6").FormulaArray = "=ROUND($J$1*SUM(IF($P$6:$P$293=A6,IF(MONTH($Q$6:$Q$293)=MONTH($E$1),$R$6:$R$293))),0)"
Range("C6:C29").FillDown

 

[Marcelo Branco]

I am unable to open the link sent by Marcelo.
Is there another way to check it out?

Do you see a warning message? If so, click the button
I understand the risk. Proceed anyway

M.

 

[asad]

T

Range("C6").FormulaArray = "=ROUND($J$1*SUM(IF($P$6:$P$293=A6,IF(MONTH($Q$6:$Q$293)=MONTH($E$1),$R$6:$R$293))),0)"
Range("C6:C29").FillDown

Thanks Jon. It worked perfect.

 

[asad]

Do you see a warning message? If so, click the button
I understand the risk. Proceed anyway

M.

Yes, after i clicked to proceed, I was able to open the link. Thanks Marcelo.

 

[asad]

Hi asad,

I can vaguely remember trying to make a dynamic range with array formulas and in the end just restored to do doing it cell-by-cell which as you only have 24 isn't an issue in this case:

Option Explicit
Sub Macro1()

    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For lngMyRow = 6 To 29
        Range("C" & lngMyRow).FormulaArray = "=ROUND($J$1*SUM(IF($P$6:$P$293=A" & lngMyRow & ",IF(MONTH($Q$6:$Q$293)=MONTH($E$1),$R$6:$R$293))),0)"
    Next lngMyRow
    
    Application.ScreenUpdating = True

End Sub

I'd be glad to know if there's a more efficient way

Regards,

Robert

Thanks a lot Robert. This also worked perfect.