| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Normal formula to array formula
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Normal formula to array formula
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Normal formula to array formula
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Normal formula to array formula
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Normal formula to array formula
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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| array to normal version formula | Excel Discussion (Misc queries) | |||
| XIRR formula in non-normal struction | Excel Worksheet Functions | |||
| Normal Text in Formula | Excel Worksheet Functions | |||
| complex excel formula Array how do I convert it to a vba Function | Excel Worksheet Functions | |||
| Convert Chart Series Formula to Array Via VBA? | Excel Programming | |||
Linear Mode
