Forum Discussion

hrh_dash's avatar
hrh_dash
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's avatar
    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
HansVogelaar's avatar
HansVogelaar
MVP
May 30, 2022

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?

  • hrh_dash's avatar
    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's avatar
      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's avatar
        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.

         

         

Resources