Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Replace array formula with VBA
Thanks Thanks: 0 Likes Likes: 0

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

    Default Replace array formula with VBA

    I am trying to replace the evaluate method with "only VBA".

    This first piece of code is a "normal" index match, in that there is only a single criteria and so is entered simply by pressing the Enter key.

    a and b return the same value, as expected.

    Code:
        Dim IndexRng As Range
        Set IndexRng = Range("A1:A2")
        
        Dim rng As Range
        Set rng = Range("B1:B2")
        
        Dim rng1 As Range
        Set rng1 = Range("E3")
        
        Dim a, b
            
        a = Evaluate("=INDEX(" & IndexRng.Address & ",Match(" & rng1.Address & "," & rng.Address & ", 0), 1)")
    
        b = Application.Index(IndexRng, Application.Match(rng1.Value, rng, 0), 1)
    
    




    This second piece of code is an array formula because there are multiple criteria and is entered by pressing Ctrl+Shift+Enter.

    c returns the correct value but d errors.


    Code:
        Dim IndexRng As Range
        Set IndexRng = Range("A1:A2")
        
        Dim rng As Range
        Set rng = Range("B1:B2")
        
        Dim rng2 As Range
        Set rng2 = Range("C1:C2")
        
        Dim rng3 As Range
        Set rng3 = Range("E3")
        
        Dim rng4 As Range
        Set rng4 = Range("F3")
        
        Dim c, d
        
        c = Evaluate("=INDEX(" & IndexRng.Address & ",Match(1, (" & rng3.Address & " = " & rng.Address & ") * (" & rng4.Address & "=" & rng2.Address & "),0))")
        
        d = Application.Index(IndexRng, Application.Match(1, (rng3 = rng) * (rng4 = rng2), 0, 1))
    
    


    The error message is:

    Code:
    Run-time error '13':
    
    Type mismatch
    How can I change d to make it work?

    Thanks
    Last edited by tiredofit; Sep 20th, 2019 at 07:12 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Replace array formula with VBA

    You can change the formula to a non CSE formula like
    =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))

    BUT you said you wanted "only VBA", neither version is "only VBA" as you are using worksheet Functions.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Replace array formula with VBA

    To omit the evaluation you would need to loop through the rows checking each one individually until a match is found.
    Code:
        Dim IndexRng As Range, crit1 As Range, crit2 As Range, rw As Range, d As Variant
        
        Set IndexRng = Range("A1:C3")
        Set crit1 = Range("E3")
        Set crit2 = Range("F3")
        
        For Each rw In IndexRng.Rows
            If rw.Cells(1, 2).Value = crit1.Value And rw.Cells(1, 3).Value = crit2.Value Then
                d = rw.Cells(1, 1).Value
                Exit For
            End If
        Next
    Debug.Print d

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

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    You can change the formula to a non CSE formula like
    =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))

    BUT you said you wanted "only VBA", neither version is "only VBA" as you are using worksheet Functions.

    Thanks.

    Are you saying to write this:

    Code:
    d =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))
    


    I did that and got a compile error.

    Quote Originally Posted by jasonb75 View Post
    To omit the evaluation you would need to loop through the rows checking each one individually until a match is found.
    Code:
        Dim IndexRng As Range, crit1 As Range, crit2 As Range, rw As Range, d As Variant
    Quote Originally Posted by jasonb75 View Post
    Code:
        Set IndexRng = Range("A1:C3")
        Set crit1 = Range("E3")
        Set crit2 = Range("F3")
    
        For Each rw In IndexRng.Rows
            If rw.Cells(1, 2).Value = crit1.Value And rw.Cells(1, 3).Value = crit2.Value Then
                d = rw.Cells(1, 1).Value
                Exit For
            End If
        Next
    Debug.Print d


    Thanks but I've got too much data, so didn't want to loop.



    Last edited by tiredofit; Sep 20th, 2019 at 07:51 AM.

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

    Default Re: Replace array formula with VBA

    A loop might be faster than an array.

    An array has to process every row of data in the range. The loop stops when it finds a match so it might only need to process a small percentage of the data.

    Another option would be to apply filters by vba based on the criteria in E3 and F3, then grab the value of the first visible row.

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

    Default Re: Replace array formula with VBA

    I was under the impression that using index / match or vlookup is quicker than looping but I could be wrong

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Replace array formula with VBA

    Quote Originally Posted by tiredofit View Post
    Are you saying to write this:

    Code:
    d =INDEX(A1:A2,MATCH(E3&"|"&F3,INDEX(B1:B2&"|"&C1:C2,0),0))
    Nope, that is the worksheet formula, so you would need to convert it to VBA in the same way you have with the two formulae you showed.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    Nope, that is the worksheet formula, so you would need to convert it to VBA in the same way you have with the two formulae you showed.
    OK, so I did this:

    Code:
        d = Application.Index(IndexRng, Application.Match(rng3 & "|" & rng4, Application.Index(rng & "|" & rng2, 0), 0))
    
    


    replacing the E3 with rng3, F3 with rng4, etc.

    but I still got a Type mismatch error.

    BTW, what's the significance of the |?



  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: Replace array formula with VBA

    It looks like VBA doesn't like handling array formulae in that manner. I suspect you will either need to use Evaluate, or ditch the functions & use pure VBA as suggested by Jason.
    The "|" is just a delimiter to prevent false positives, for instance if you had these values in E & F col G shows what you would get without a delimiter

    EFG
    3123ABCD123ABCD
    4123ABCD123ABCD

    Sales





    ie exactly the same thing.

    How many rows of data do you have?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Replace array formula with VBA

    Quote Originally Posted by Fluff View Post
    It looks like VBA doesn't like handling array formulae in that manner. I suspect you will either need to use Evaluate, or ditch the functions & use pure VBA as suggested by Jason.
    The "|" is just a delimiter to prevent false positives, for instance if you had these values in E & F col G shows what you would get without a delimiter

    E F G
    3 123 ABCD 123ABCD
    4 123A BCD 123ABCD
    Sales



    ie exactly the same thing.

    How many rows of data do you have?

    Thanks for the explanation.

    Prob a few hundred thousand rows of data!

    Using pure VBA took ages, so I resorted to array formulae and index match / vlookups instead of looping, which vastly improved the running time.

    My latest attempt is to let Access do the reconciling. It's super fast but the problem is having to import the data from Excel into the Access tables, which takes more time than the rec itself.

    Another obstacle is the 2GB limit in Access.

    So perhaps I'll have to ask for SQL Server.



    Last edited by tiredofit; Sep 20th, 2019 at 08:55 AM.

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
  •