过滤范围上的vlookup

Question

我需要使用VBA在过滤范围内进行Vlookup。我经历了在同一主题上发布的不同主题，但是他们实际上无法回答我的问题。

基本上，我需要执行查找的工作表，将通过用户输入接收。我的代码如下：

Option Explicit

Sub VlookATM()

Dim lookFor As String
Dim srchRange As Range
Dim PMTPathWs As Workbook
Dim book2 As Workbook
Dim CurrentWs As Worksheet, WorkersWs As Worksheet
Dim ATMpathWs As Worksheet
Dim CurrentLastRow As Long, WorkersLastRow As Long, lastrow As Long, X As Long, d As Integer
Dim workerRange As Range
Dim path As String



path = InputBox("Please enter the ATM file path with extension")
Set ATMpathWs = Workbooks.Open(Filename:=path)
Set CurrentWs = ThisWorkbook.Worksheets("Current_Data")
Set WorkersWs = ThisWorkbook.Worksheets("Active_Workers")

CurrentLastRow = CurrentWs.Range("A" & Rows.Count).End(xlUp).Row
ATMpathWs.Activate

CurrentWs.UsedRange.AutoFilter 4, "ATM Testing", xlFilterValues

ActiveCell.Resize(lastrow - ActiveCell.Row + 1).SpecialCells(xlVisible).FormulaR1C1 = _
"=VLOOKUP(RC[-5],'[ATMpathWs]Charge Type Wise Effort Report'!R9C5:R23C9,5,0)"



End Sub

当我运行代码时，大多数情况下它没有丢弃任何错误，在文件中，我可以看到行被过滤，但是查找未完成。另外，有时代码丢弃运行时错误。

有人可以对此提供一些启示吗？