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

 

 

 

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

Resources