Issue Adding an Array Formula to a cell at Runtime.

[Labkhand]

Hi All,

In my VB code, I assign different formulas to several columns. If the
formula is not an Array formula, I am okay. But, I have an issue whenever I
am assigning an array formula to a cell.

For an array formula it is required to hold Ctrl + Shift, and press the
Enter key to see the result in the cell.

When I run my code, it inserts the array formula in the cell, but it doesn't
show the result until I manully go to the cell and do the Ctrl + Shift, and
press the Enter combinations.

How can I make the result on this cell be automatically calculated through
the VB code without any manuual process that I am doing?

Thanks for your help in advance.

 

[Mike H]

Hi,

had you posted you array formula then it would have been easier to help but
this may get you going in the right direction.

Range("A1").FormulaArray = "=AVERAGE(IF(C1:C11>0,C1:C11))"

Mike

 

[Gary''s Student]

Sub qwerty()
Range("A1").FormulaArray = "=1+2"
End Sub

 

[Labkhand]

Mike,

here is my formula:

strFormula = "=INDEX(DetailData,MATCH(1, (D_Date=DATEVALUE(TEXT($B41-1,"_ &
"""mmm-yyyy"")))*(D_BankName=$C21)* " & _
"(D_AccountType=$D21)*(D_TransType=" & """Current Balance""),0),MATCH(" &
"""Current Balance"",$A$1:$K$1,0))"

which basically is "=INDEX(DetailData, 2,10)" formula. The array formula is
the first MATCH which I am using and is causing me a headache.

thanks agian for your help

 

[Labkhand]

Thanks Mike and Gary for your response. I was able to resolve this issue
with your help.