Results 1 to 9 of 9

Thread: VBA array with array formulae
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default VBA array with array formulae

    In excel, this is perfectly valid:

    Code:
    =MATCH(1,F1=C1:C100)*(G1=D1:D100),0)
    when entered as an ARRAY formula, using Ctrl+Shift+Enter.

    Is it possible to use a similar syntax in VBA arrays?

    For example:

    Code:
    Dim FirstRng As Range
    Set FirstRng=Range("C1:C100")
    
    Dim SecondRng As Range
    Set SecondRng=Range("D1:D100")
    
    OutputArray(Counter, 1) = Application.WorksheetFunction.Match(1, (StartArray(Counter, 6) = FirstRng) * (StartArray(Counter, 7) = SecondRng), 0 )
    
    


    I am getting a Type mismatch error.

    Thanks


    Last edited by tiredofit; Aug 28th, 2019 at 05:43 AM.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,358
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA array with array formulae

    Hi,

    You can test following

    Code:
    MsgBox Evaluate("=MATCH(1,F1=C1:C100)*(G1=D1:D100),0)")
    Hope this will help

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA array with array formulae

    Type mismatch would incicate an incorrect data type rather than an error in the syntax, possibly one of the array variables not declared correctly?

    Personally, I would try using the Application.Evaluate method, which should work as an array.
    Code:
    OutputArray(Counter, 1) = [Match(1, (StartArray(Counter, 6) = FirstRng) * (StartArray(Counter, 7) = SecondRng), 0 )]
    or an alternative
    Code:
    OutputArray(Counter, 1) = [AGGREGATE(15,6,ROW(FirstRng) / (StartArray(Counter, 6) = FirstRng) / (StartArray(Counter, 7) = SecondRng), 1)]
    I think that the syntax is correct.

  4. #4
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA array with array formulae

    Thanks for the suggestions.

    This worked:

    Code:
    OutputArray(Counter, 1) = [Match(1, (StartArray(Counter, 6) = FirstRng) * (StartArray(Counter, 7) = SecondRng), 0 )]


    What is the significance of using square brackets?

    Seems to NOT work without them.





    Last edited by tiredofit; Aug 28th, 2019 at 06:19 AM.

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA array with array formulae

    The square brackets are a shorthand command telling vba to evaluate what is between them as a formula.

    https://docs.microsoft.com/en-us/off...ation.evaluate

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA array with array formulae

    Thanks again.

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,358
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA array with array formulae

    Hi,

    Have you tested Evaluate ...???

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA array with array formulae

    James, see post 5.

    Square brackets are a simplified version of evaluate.

  9. #9
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,358
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA array with array formulae

    Quote Originally Posted by jasonb75 View Post
    James, see post 5.

    Square brackets are a simplified version of evaluate.

    Thanks ... but question asked because of ...

    https://www.mrexcel.com/forum/excel-...e-formula.html

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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