Troubleshooting VBA Code Error and Clarifying Steps for Data Filtering and Formulas

Dear Experts,

I've been working with a VBA macro generated using ChatGPT that filters data based on specific criteria and retrieves additional information from an external workbook using INDEX and MATCH functions. However, I've encountered an error. I suspect the error are related to the functions.

I hope someone here can assist me with troubleshooting this issue.

I have attached a sample worksheet for your reference to facilitate understanding.

Thank you in advance for your support!!!


The steps required in the VBA are as follows:

1. Filter Data: Show only the rows where:
- Column AD is empty.
- Column Z has "SMBC"

2. Check for Data: Verify if any rows match the filter criteria. If no rows match, display a message stating that no data was found and stop the process. If there is a match, proceed to step 3.

3. Formulas are applied to columns AD and AE to fetch data from this external workbook using INDEX and MATCH functions.
Which is to retrieve data from columns K and M of an external workbook based on matches in column P.

Path to an external workbook --> "R:\Shipping Group- SMBC INCOMING FUND REPORTS.xlsm".

4. Clear Filter.

5. Convert Formulas to Values: Convert the formulas in columns AD and AE into fixed values to prevent them from changing if the data in the external file changes.

Sub SMBCFUNDS()
'
' SMBC INCOMING FUNDS UPDATE - 25/6/24 version 1
'

    Application.ScreenUpdating = False
    
    With ActiveSheet
        .AutoFilterMode = False
        .Rows("4:4").AutoFilter
        
        With .Range("$A$5:$BG$" & .Cells(.Rows.COUNT, 1).End(xlUp).Row)
            .AutoFilter Field:=30, Criteria1:="="
            .AutoFilter Field:=26, Criteria1:="SMBC"
        End With

        On Error Resume Next
        Dim visibleRows As Long
        visibleRows = .Range("$A$5:$A$" & .Cells(.Rows.COUNT, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).COUNT
        On Error GoTo 0
        
        If visibleRows <= 1 Then
            .ShowAllData
            MsgBox "No rows found matching the criteria. Process stopped."
            Application.ScreenUpdating = True
            Exit Sub
        End If

        Dim wbPath As String
        wbPath = "R:\Shipping Group\- SMBC INCOMING FUND REPORTS.xlsm"
        
        Dim lastRow As Long
        lastRow = .Cells(.Rows.COUNT, 1).End(xlUp).Row
        
        With .Range("AD5:AE" & lastRow).SpecialCells(xlCellTypeVisible)
            .FormulaR1C1 = "=IFERROR(INDEX('" & wbPath & "'!$K:$K, MATCH(RC[-22],'" & wbPath & "'!$P:$P, 0)), """")"
            .Offset(, 1).FormulaR1C1 = "=IFERROR(INDEX('" & wbPath & "'!$M:$M, MATCH(RC[-23],'" & wbPath & "'!$P:$P, 0)), """")"
        End With
        
        Application.Calculation = xlCalculationManual
        
        .Range("AD5:AE" & lastRow).Value = .Range("AD5:AE" & lastRow).Value

    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "Payment Updated!"
End Sub