Issue Adding an Array Formula to a cell at Runtime.

  • Thread starter Thread starter Labkhand
  • Start date Start date
L

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.
 
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
 
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
 
Thanks Mike and Gary for your response. I was able to resolve this issue
with your help. :)
 
Back
Top