How do i create a vba code for a index match with 2 different workbooks without opening the workbook

[hrh_dash]

I would like to create an iferror index match VBA code by taking reference from 2 different workbooks; export and clRequest.

The first index match would be looking up from export workbook. If there is an error, it would then look up from clRequest workbook.

If there is an error after looking up from both export and clRequest workbooks then it would pop up a display message box stating "Credit Limit is not allocated to this customer."

It would great if both 2 workbooks remain closed when the code is executed.

Currently I am receiving a run time error 1004 pop up

Sub Index_Match()
Dim ws As Worksheet 'current sheet
Dim exportWb As Workbook
Dim clRequestWb As Workbook
Dim First As Variant
Dim Second As Variant

Set ws = Sheet1
Set exportWb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx")

Set clRequestWb = "V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\1. Credit Limit Requests\Credit Limit Requests (no existing SAP IDs).xlsx"
Set clRequestWb = ThisWorkbook.Activate

First = ws.Cells(14, 3).Value
ws.Cells(14, 3).Value = Application.WorksheetFunction.Index(exportWb.Sheets("Sheet1").Range("B:B"), Application.WorksheetFunction.Match(Cells(15, 3), exportWb.Sheets("Sheet1").Range("E:E"), 0))

Second = ws.Cells(14, 3).Value
ws.Cells(14, 3).Value = Application.WorksheetFunction.Index(clRequestWb.Sheets("No SAP ID").Range("A:A"), Application.WorksheetFunction.Match(Cells(15, 3), clRequestWb.Sheets("No SAP ID").Range("B:B"), 0))

If IsError(First) Then
    Second
  
If IsError(Second) Then
MsgBox "Credit Limit is not allocated to this customer"

End If
End If

End Sub

[#2]

Hi @Hans Vogelaar , would you be able to assist on this? 

[#3]

@hrh_dash 

The lines

Set clRequestWb = "V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\1. Credit Limit Requests\Credit Limit Requests (no existing SAP IDs).xlsx"
Set clRequestWb = ThisWorkbook.Activate

make no sense. The first tries to set a Workbook variable to a string, and the second uses Activate.

Should clRequestWb be the workbook containing the code, or an external workbook?

[#4]

@Hans Vogelaar , thanks for reverting.

Credit assessment workbook will be the workbook containing the code.

Index match will be looking up at values from both export and clRequest workbook.

I am not sure how to write the code so that the index match could perform a lookup from export workbook and if there is an error lookup data from clRequest workbook.

This would be the excel formula:
=IFERROR(INDEX([export.XLSX]Sheet1!$B:$B,MATCH(C15,[export.XLSX]Sheet1!$C:$C,0)),INDEX('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$A:$A,MATCH(C15,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$B,0)))

[#5 · Accepted solution]

@hrh_dash 

Does this work?

Sub Index_Match()
    Dim ws As Worksheet 'current sheet
    Dim exportWb As Workbook
    Dim clRequestWb As Workbook
    Dim v As Variant

    Set clRequestWb = ThisWorkbook
    Set ws = clRequestWb.Worksheets("Sheet1")
    Set exportWb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx")

    v = Application.Match(ws.Cells(15, 3), exportWb.Sheets("Sheet1").Range("E:E"), 0)
    If IsError(v) Then
        v = Application.Match(ws.Cells(15, 3), clRequestWb.Sheets("No SAP ID").Range("B:B"), 0)
        If IsError(v) Then
            MsgBox "Credit Limit is not allocated to this customer"
        Else
            ws.Cells(14, 3).Value = Application.Index(clRequestWb.Sheets("No SAP ID").Range("A:A"), v)
        End If
    Else
        ws.Cells(14, 3).Value = Application.Index(exportWb.Sheets("Sheet1").Range("B:B"), v)
    End If
End Sub

[#6]

@Hans Vogelaar , my apologies for wasn't being clear previously. 

In total, i have 3 workbooks.

1st workbook: exportWb (C:\Users\hrhquek\Desktop\export.xlsx) - where i will be looking up the values from column B

2nd workbook: clRequestWb (V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\1. Credit Limit Requests\Credit Limit Requests (no existing SAP IDs).xlsx - where i will be looking up values from column A if no data is found from exportWb

3rd workbook: existing workbook which will contains the macro.

your code was based on the macros are entered into clRequestWb.

[#7]

@Hans Vogelaar , i managed to fix the code and have the values populated. however, is there a way to run the code without opening both exportWb and clRequestWb?

The code so far:

Sub Index_Match()
Dim ws As Worksheet 'current sheet
Dim exportWb As Workbook
Dim clRequestWb As Workbook
Dim x As Variant

Set ws = Sheet1
Set exportWb = Workbooks.Open("C:\Users\hrhquek\desktop\export.xlsx")

Set clRequestWb = Workbooks.Open("V:\Finance\Systems-Risk-ERM\OrderToCash\C2C\Corp Credit Control\1. Credit Limit Requests\Credit Limit Requests (no existing SAP IDs).xlsx")
Set clRequestWb = ActiveWorkbook
ThisWorkbook.Activate

x = Application.Match(ws.Cells(15, 3), exportWb.Sheets("Sheet1").Range("E:E"), 0)


 If IsError(x) Then
    x = Application.Match(ws.Cells(15, 3), clRequestWb.Sheets("No SAP ID").Range("B:B"), 0)

If IsError(x) Then
    MsgBox "Credit Limit is not allocated to this customer"
Else
    ws.Cells(14, 3).Value = Application.WorksheetFunction.Index(clRequestWb.Sheets("No SAP ID").Range("A:A"), Application.WorksheetFunction.Match(Cells(15, 3), clRequestWb.Sheets("No SAP ID").Range("B:B"), 0))

End If
Else
   ws.Cells(14, 3).Value = Application.WorksheetFunction.Index(exportWb.Sheets("Sheet1").Range("B:B"), Application.WorksheetFunction.Match(Cells(15, 3), exportWb.Sheets("Sheet1").Range("E:E"), 0))
End If
End Sub

[#8]

@hrh_dash 

I don't know if it's possible at all, but if so, it would be complicated. Opening the workbooks is a lot simpler. You can close them at the end of the macro, of course.

[#9]

@Hans Vogelaar , thanks for the assist. Appreciate the help