Array Formula

[vmjan02]

Hi,

I have this code and its not working on VBA, but applied with Ctrl+Shift+enter manual its works.

With Worksheets("Sheet2") 'Rank
    With .Range("G5:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .FormulaArray = "=IF(E5="""","""",COUNTIF($E$5:$E$8000,"">""&E5)+SUM(IF(E5=$E$5:E5,1,0)))" --- Same is not working
        .Value = .Value
    End With
End With

 

[James006]

Hi,

You could test :

.FormulaArray = _
        "=IF(R[-94]C[3]="""""""","""""""",COUNTIF(R5C5:R8000C5,"""">""""&R[-94]C[3])+SUM(IF(R[-94]C[3]=R5C5:R[-94]C[3],1,0)))"

HTH

 

[RoryA]

Your code is the equivalent of selecting all those cells and then array-entering the same formula into all of them; it is not the same as array entering the formula into G5 and then filling that down. What you need is:

With Worksheets("Sheet2") 'Rank
    .Range("G5").FormulaArray = "=IF(E5="""","""",COUNTIF($E$5:$E$8000,"">""&E5)+SUM(IF(E5=$E$5:E5,1,0)))"
    With .Range("G5:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .FillDown
        .Value2 = .Value2
    End With
End With

 

[vmjan02]

ops Rory, thanks I missed the part as need to enter the array formula first and then fill down. Thank a

 

[vmjan02]

Thanks James, got the solution.