+ Reply to Thread
Results 1 to 5 of 5

Convert Normal formula to array formula

  1. #1
    Pradip Jain
    Guest

    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. #2
    Leo Heuser
    Guest

    Re: 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" <PradipJain@discussions.microsoft.com> skrev i en meddelelse
    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. #3
    Pradip Jain
    Guest

    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:

    > 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" <PradipJain@discussions.microsoft.com> skrev i en meddelelse
    > 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. #4
    Herbert
    Guest

    Re: 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" <PradipJain@discussions.microsoft.com> skrev i en meddelelse
    > > 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. #5
    Pradip Jain
    Guest

    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,
    >
    > 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" <PradipJain@discussions.microsoft.com> skrev i en meddelelse
    > > > 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
    > > >
    > > >
    > > >
    > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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