+ Reply to Thread
Results 1 to 21 of 21

Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

  1. #1
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    I am totally confused with this one...

    If I use this then its entering the formula in A1 cell and I have to press CTRL+SHIFT+ENTER to make it as array and its returning the right result.

    Please Login or Register  to view this content.
    The result of the above formula in A1 cell is
    =INDEX('Leave Credit'!$D$2:$D$8,SMALL(IF(('Leave Credit'!$B$2:$B$8='Leave card'!$D$10)*('Leave Credit'!$D$2:$D$8>='Leave card'!$C$8)*('Leave Credit'!$D$2:$D$8<='Leave card'!$F$8),ROW('Leave Credit'!$D$2:$D$8)-ROW('Leave Credit'!$D$2)+1),ROW(A1)))

    After CTRL+SHIFT+ENTER it is returning the right results.

    But when I use evaluate with this

    Please Login or Register  to view this content.
    It's returning #Value as result....

    If I use it in this way

    Please Login or Register  to view this content.
    Then it's returning...

    Run-time error 1004
    Unable to set the formulaArray property of the range class.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Formulaarray only works with R1C1 references. Your address properties will return A1 style references.

    Edit: you might be able to get around this with:
    Please Login or Register  to view this content.
    but I don't know if it will work.
    Last edited by ragulduy; 05-30-2013 at 04:49 AM.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    @ yudlugar,

    Thanks for the suggestion and it's working fine by following the below way...

    Please Login or Register  to view this content.
    But now my issue is I am using this with an UDF, I just shown Range("A1") for easy understanding just worrying how I have to use it with an variable

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Try replacing
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    I think, the fourth arguement is the reference style and false means R1C1.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Thanks for the suggestion but keeping the reference as R1C1 is not running the formula as array...

    Please Login or Register  to view this content.
    Even I tried

    Please Login or Register  to view this content.
    But not converting as array getting #Value error...

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Quote Originally Posted by Norie View Post
    Try this.
    Please Login or Register  to view this content.
    @ Norie,

    Thanks for the suggestion

    But in formula I want to have the sheet reference too.. I am unable to get the sheet reference other than marking the forth argument external as True, any other alternate suggestion for this one will be helpful since setting the external property as true getting the file name too...

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    I couldn't actually work out which argument it was, all those commas confused me.

    Did you try adding External:=True?

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    @ Norie,

    Yes, like I shown in Post #6, I turned on both R1C1 ref and external ref as true but not sure how I can convert this as array and assign the value to a variable

  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    What I seen in the Address argument the third one is Reference Style and the Fourth argument is External reference and I made it in the same way in Post #6, but no luck

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    Why not use Named arguments?

    It works for me.

    This returns [Book1]Sheet1!R1C1:R10C1.
    Please Login or Register  to view this content.
    Last edited by Norie; 05-30-2013 at 06:25 AM.

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Actually Norie this is not for me This is for the below thread I am working for...

    http://www.excelforum.com/excel-form...71#post3255871

    Please refer the Leave Card Sheet of attached file...

    The OP is a 2003 user and the formula which we have suggested seems to be a hectic approach to the OP, so I thought to create an UDF so that the OP can just select the ranges and the error handler and the lengthy formula's will be taken care by the VBA coding...

    Please refer the A18 to B23 range of Leave Card Sheet where the suggested formula is applied...

    I would like to evaluate the same formula using an UDF and trying to get the A18 cell value in A7 GetResult UDF...

    I hope it's clear now... Just wondering how I can use array combination and store it in a Variable
    Attached Files Attached Files

  13. #13
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    I have not looked at the workbook but evaluate is limited to 255 characters and I suspect your external reference style is taking you over that limit
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  14. #14
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    @ JosephP,

    Thanks for pointing it and me too read that in Microsoft site whether anyother method which gets the reference without filename and including the sheet reference or we have to use Instr() function and split and assign the reference in a variable and refer it in formula?

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Little confused.

    I was just trying to help with the problem you were having with this formula and converting it to R1C1 as yudlugar suggested.

    When I look at that workbook it kind of looks like it could all, or a lot of it anyway, be done in code.

    Mind you there's 3 pages of posts that I've not looked through, so I've probably totally missed the plot.
    Last edited by Norie; 05-30-2013 at 09:22 AM.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    SixthSense

    If you want to get the worksheet name use <worksheet>.Name or if you don't have a worksheet reference use <range>.Parent.Name

  17. #17
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Perhaps simply
    Please Login or Register  to view this content.

  18. #18
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    @ Norie,

    Forget about that link... Yeah even I won't do that when the thread have lot of posts...

    I just want to convert the A18 cell formula of that file to a UDF... But not getting the method about how to use a formula in a variable as array...

  19. #19
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Quote Originally Posted by JosephP View Post
    Perhaps simply
    It's working fine Joseph, I am just thinking why microsoft is not included another argument which enables/disables the workbook name in address output.

    Thanks a lot for Norie for suggesting some more methods of referencing to the worksheet

    One more question, dragging the UDF down is not reacting in the same way like using the row(a1) in evaluate function. The Row(a1) stays as it is and returning 1 as result and due to this the small function results the first lowest match.. any other method can be used instead of Row(a1)

    Thanks a lot for both of you for providing your valuable suggestions/solutions

  20. #20
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    The ROW(A1) is fixed in the code-perhaps pass it as an additional argument

  21. #21
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Unable to get result Using FormulaArray or Evaluate with Array Formula in VBA

    Ah!! Don't know how I forgot that method Thanks for the right suggestion

+ 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