Hi,

this might not help you but your code works, at least for me.
The statement
ActiveCell.FormulaArray = ActiveCell.Formula
changed my normal formula into an array formula

Regards,
Herbert

"Pradip Jain" wrote:

Does not work.
Error: Run-time error '1004': Unable to set the FormulaArray property of the
Range class


"Leo Heuser" wrote:

Pradip

One way:

Sub test()
Dim Form As String

With Range("G257")
Form = .Formula
.FormulaArray = Form
End With
End Sub

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pradip Jain" skrev i en meddelelse
...
Cell g257 contains the following formula

="=(sum(("&$AW257&"$B$2:$B$2000="&""""&$B$2&""""&" )*("&$AW257&"$C$2:$C$2000="&AH257&")*("&$AW257&"$E $2:$E$2000="&""""&G$1&""""&")*("&$AW257&"$K$2:$K$2 000)))*"&$AP257

When I copy and paste the formula as value, i get the following:
=(SUM(('D:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$B$2:$B$2000="NIFTY")*('D:\INVESTM ENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$C$2:$C$2000=37651)*('D:\INVESTMEN TS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$E$2:$E$2000="CE")*('D:\INVESTMENT S\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$K$2:$K$2000)))*200

Now since the above formula is an array formula, I need to convert that to
array formula by typing Ctr+shift+enter. This gives the required result.

I want to automate the process through a macro (since there are several
thousand cells which i need to convert from ordinary formula to array
formula). Assuming I have done copy and paste as values manually, I am
trying
the following code for one particular cell.

Sub Macro2()

Range("G257").Select
Selection.FormulaArray = ActiveCell.Formula

End Sub

This code is not working. Can anyone help me with code to convert normal
formula to array formula.

Thanks a lot