VBA help needed to get FormulaArray from cell value

[Akbarov]

Hello Dear community,

I have cells with array formulas I used sign ' to store them as text. So depending on users choice VBA needs to insert that array formula to cell AA10.
Example, this formula is in range A4:
'='sales data'!AA10*RAND()*10000000

I want to insert this array formula to sheet ws.range("AA10")
I used this code:

ws.Range("AA10").FormulaArray = Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).Formula
'And also tried'
ws.Range("AA10").FormulaArray = Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).value

But it doesn't works. Can anyone help me please?

 

[bebo021999]

I do not think this:
='sales data'!AA10*RAND()*10000000
is an array formula.

May be try with .Formula only?
ws.Range("AA10").Formula = ...

 

[Akbarov]

Hello bebo, thanks for reply, but problem is , it is in most times array, but sometimes regular formula.

 

[Tetra201]

A couple of questions:
1) If it's in cell A4, why do you need Range("A" & Cell.Row).Offset(, 1)?
2) What does the "doesn't work" mean?

 

[Akbarov]

Thanks for reply. I could figure out how to make it can work. Used IF statement to check whether cell hasFormula or hasArray.
And yes formulas actually on column B, that's why I used offset.

         If Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).HasArray = True Then
                            ws.Range("AA10").FormulaArray = Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).FormulaArray
                            Sheets("ArrayBackend").Calculate
                            ElseIf Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).HasFormula = True Then
                            ws.Range("A10:" & Cells(LR, LC).Address).Formula = Sheets("ArrayBackend").Range("A" & Cell.Row).Offset(, 1).Formula
                            End If

 

[Tetra201]

Glad you found a solution.