INDEX / MATCH Function In VBA Code

[taccoo73]

Hi All;

First of all, thanks a lot in advance for your time and help. Here is my question;

I'm trying to use Excel's Index and Match functions within VBA code to retrieve data from another workbook, based on a criteria in a cell. The formula below works without any problem and brings me the value I need;

=INDEX('[ORMB NEW.xlsm]CD'!$B$3:$B$637,MATCH(B3-1,'[ORMB NEW.xlsm]CD'!$A$3:$A$637,0))

And I'm using the code below to do the same thing with VBA code;

Sub cflow()
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rptdate As Variant
Dim begin As Long
 
rptdate = Range("B3") - 1
Set wkb = Workbooks("ORMB NEW.xlsm")
Set wks = wkb.Worksheets("CD")
 
begin = Application.WorksheetFunction.Index(Sheets(wks).Range("B3:B637"), Application.WorksheetFunction.Match(rptdate, Sheets(wks).Range("A3:A637"), 0))
 
Range("B5") = begin
 
End Sub

When I run the code above, I get Type Mismatch error. (The line with “begin=” highlighted)

Criteria in B3 is a date and formatted as dd-mmm-yy
Source range A3:A637 formatted exactly the same
Source range B3:B637 formatted as number

Tried variant, long, string with no luck so far and I have no idea what I'm missing.

Any help will be much appreciated.

Regards;

taccoo

 

[steve the fish]

Dates can be a bit weird with match. Try making rptdate as Long. But then check that the value in the range it needs to match it in is truely a date and not a text string.

 

[taccoo73]

You're a wizard Steve !! Long did not work on my original code but it works after your corrections

Million times thanks!!!

 

[steve the fish]

Just for reference you could also have used Evaluate:

Set wkb = Workbooks("ORMB NEW.xlsm")
Set wks = wkb.Worksheets("CD")
Range("B5") = Evaluate("INDEX('[" & wkb.Name & "]" & wks.Name & "'!$B$3:$B$637,MATCH(B3-1,'[" & wkb.Name & "]" & wks.Name & "'!$A$3:$A$637,0))")

or even:

Range("B5") = Evaluate("INDEX('[ORMB NEW.xlsm]CD'!$B$3:$B$637,MATCH(B3-1,'[ORMB NEW.xlsm]CD'!$A$3:$A$637,0))")

 

[taccoo73]

Thanks a lot Steve, I will give that last one a try for sure. Appreciate the great help!!