# Convert Normal formula to array formula

1. ## 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\$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

2. ## Re: Convert Normal formula to array formula

One way:

Sub test()
Dim Form As String

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

--
Best Regards
Leo Heuser

news:B3EE8AE6-CF7B-44C0-9680-F7E03450FFEB@microsoft.com...
> 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

3. ## Re: 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:

>
> 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.
>
> news:B3EE8AE6-CF7B-44C0-9680-F7E03450FFEB@microsoft.com...
> > 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

>
>
>
>

4. ## Re: Convert Normal formula to array formula

Hi,

The statement
ActiveCell.FormulaArray = ActiveCell.Formula
changed my normal formula into an array formula

Regards,
Herbert

> Does not work.
> Error: Run-time error '1004': Unable to set the FormulaArray property of the
> Range class
>
>
> "Leo Heuser" wrote:
>
> >
> > 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.
> >
> > news:B3EE8AE6-CF7B-44C0-9680-F7E03450FFEB@microsoft.com...
> > > 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

> >
> >
> >
> >

5. ## Re: 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,
>
> The statement
> ActiveCell.FormulaArray = ActiveCell.Formula
> changed my normal formula into an array formula
>
> Regards,
> Herbert
>
>
> > Does not work.
> > Error: Run-time error '1004': Unable to set the FormulaArray property of the
> > Range class
> >
> >
> > "Leo Heuser" wrote:
> >
> > >
> > > 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.
> > >
> > > news:B3EE8AE6-CF7B-44C0-9680-F7E03450FFEB@microsoft.com...
> > > > 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
> > >
> > >
> > >
> > >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1