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]

In much the same way as you used wkb to qualify the sheet in wkb.worksheets you just need wks.Range not Sheets(wks).Range

 

[taccoo73]

Thanks for the reply steve, appreciate it.

When I change the code to wks.Range, it gives me 400 error, means cannot find the sheet.

 

[steve the fish]

Ok post the whole code with the changes.

 

[taccoo73]

Here it is;

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(wks.Range("B3:B637"), Application.WorksheetFunction.Match(rptdate, wks.Range("A3:A637"), 0))


Range("B5") = begin


End Sub

 

[jbesclapez]

Dont want to oversimplify, but why dont you use that : Range("B3").Formula = "INDEX('[ORMB NEW.xlsm]CD'!$B$3:$B$637,MATCH(B3-1,'[ORMB NEW.xlsm]CD'!$A$3:$A$637,0))"
and then you copy paste it as value in VBA?

 

[steve the fish]

What does this give?

Sub cflow()
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rptdate As Variant
Dim begin As Long

On Error GoTo errhandler


rptdate = Range("B3") - 1
Set wkb = Workbooks("ORMB NEW.xlsm")
Set wks = wkb.Worksheets("CD")


begin = Application.WorksheetFunction.Index(wks.Range("B3:B637"), Application.WorksheetFunction.Match(rptdate, wks.Range("A3:A637"), 0))


Range("B5") = begin

Exit Sub

errhandler:
MsgBox Err.Number & ":" & Err.Description

End Sub

 

[taccoo73]

It gives "1004: Unable to get the Match property of the WorksheetFunction class"

 

[steve the fish]

Ok so that is saying it cant find rptdate within the wks.Range("A3:A637"). Add:

MsgBox rptdate

to the code just before the line begin = etc etc. Is rptdate as you would expect value wise? You dont qualify the sheet when you assign a value to this variable by the way.

 

[taccoo73]

The date in B3 (active sheet when the code starts to run) is 31-Dec-17, and the message box is saying that rptdate is 30-Dec-17. 30-Dec-17 exist on the source sheet (ORMB book, CD sheet) at cell A271.

If I Dim rptdate as variant, it is 30-Dec-17, if I will Dim it as Long, it's 43099 and if I will Dim it as String it's "30-Dec-17". I have a feeling that there's something with the declaration I'm using, but cannot figure it out what.