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.
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:
How can I change d to make it work?Code:Run-time error '13': Type mismatch
Thanks
Last edited by tiredofit; Sep 20th, 2019 at 06:12 AM.
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
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
Last edited by tiredofit; Sep 20th, 2019 at 06:51 AM.
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.
I was under the impression that using index / match or vlookup is quicker than looping but I could be wrong
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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?
- Posting Data try one of these tools
- Posting guidelines, forum rules and terms of use
- Read the FAQs
Running Office 365 on Win 10
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 07:55 AM.
Like this thread? Share it with others