INDEX / MATCH Function In VBA Code

taccoo73

New Member
Joined
Feb 14, 2014
Messages
21
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;

Code:
=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;

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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
Here it is;
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(wks.Range("B3:B637"), Application.WorksheetFunction.Match(rptdate, wks.Range("A3:A637"), 0))


Range("B5") = begin


End Sub
 
Upvote 0
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?
 
Upvote 0
What does this give?

Code:
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
 
Upvote 0
Ok so that is saying it cant find rptdate within the wks.Range("A3:A637"). Add:

Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top