Convert Normal formula to array formula

P

Pradip Jain

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$2000)))*"&$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:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$C$2:$C$2000=37651)*('D:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$E$2:$E$2000="CE")*('D:\INVESTMENTS\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
 
L

Leo Heuser

Pradip

One way:

Sub test()
Dim Form As String

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

Pradip Jain

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


Leo Heuser said:
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 said:
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$2000)))*"&$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:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$C$2:$C$2000=37651)*('D:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$E$2:$E$2000="CE")*('D:\INVESTMENTS\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
Click to expand...
Click to expand...
 
H

Herbert

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 said:
Does not work.
Error: Run-time error '1004': Unable to set the FormulaArray property of the
Range class


Leo Heuser said:
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 said:
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$2000)))*"&$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:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$C$2:$C$2000=37651)*('D:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$E$2:$E$2000="CE")*('D:\INVESTMENTS\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
Click to expand...
Click to expand...
Click to expand...
 
P

Pradip Jain

Checked again. It doesn't work for me. Getting same error.
Error: Run-time error '1004': Unable to set the FormulaArray property of the
Range class


Herbert said:
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 said:
Does not work.
Error: Run-time error '1004': Unable to set the FormulaArray property of the
Range class


Leo Heuser said:
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" <[email protected]> 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$2000)))*"&$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:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$C$2:$C$2000=37651)*('D:\INVESTMENTS\XLS\FNO\[fo3JAN2003bhav.xls]fo3JAN2003bhav'!$E$2:$E$2000="CE")*('D:\INVESTMENTS\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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array with many cells from a row but not in order or together 11
How to use Sumifs with vertically and horizontally criterias 3
Copy Array to Array, how to and should I? 13
If Condition 4
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5
If Condition 0
sumproduct problem (another one) 3
ISODD array formula 1

Top