VBA Index-Match Function Doesn't Work Between Workbooks

[radbroman]

I'm trying to write a custom function that will allow me to index-match data from another spreadsheet (DataSource.xlsx). I pull data from this sheet a lot, so I figured it would be easier to just create a function rather than doing the index-match every time. Below is what I have so far:

Function CNC(CellRef As Range)

Dim arraywkb As Workbook
Set arraywkb = Workbooks.Open("C:\DataSource.xlsx")
Dim arraywks As Worksheet
Set arraywks = Sheets("Sheet1")

Dim targetwkb As Workbook
Set targetwkb = ThisWorkbook
Dim targetwks As Worksheet
Set targetwks = ActiveSheet

CNC = Application.Index(arraywks.Range("A1:L15000"), Application.Match(targetwks.Range(CellRef), arraywks.Range("A1:A15000"), 0), 11)

End Function

This code works perfectly if I use it in the DataSource workbook. However, I need to get it to work in any workbook I'm currently working in (the active sheet). When I try to use it in other workbooks, I get a #VALUE! error (A value used in the formula is of the wrong data type). It's as if it's not activating the DataSource sheet to pull the data. Is there any way to get this code to work correctly in other workbooks?

 

[offthelip]

You haven't referred to the Arraywkb at all which means vba is looking for arraywks in the active workbook , so it can't find it. just add arraywkb. in front of arraywks. so try this:

CNC = Application.Index(arraywkb.arraywks.Range("A1:L15000"), Application.Match(targetwks.Range(CellRef), arraywkbarraywks.Range("A1:A15000"), 0), 11)