Forum Discussion

Iron Contributor

May 26, 2022

Solved

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

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 the...

Macros and VBA

HansVogelaar

May 30, 2022

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

hrh_dash

Iron Contributor

May 30, 2022

HansVogelaar , 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)))

HansVogelaar

MVP

May 30, 2022

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

hrh_dash

Iron Contributor

Jun 04, 2022

HansVogelaar , 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.

hrh_dash

Iron Contributor

Jun 04, 2022

HansVogelaar , 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

HansVogelaar
MVP
Jun 04, 2022
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.