Hi All,
I need to vlookup on a filtered range using VBA. I have gone through different threads posted on same topic, however they can't actually answer my question.
Basically the sheet where I need to perform the lookup, will be received via User input. My code goes as follows :
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
When I run the code, most of the time it is not throwing any error and in the file I can see the rows are filtered, however the lookup is not done. Also, there are times when the code is throwing runtime error.
Can anyone please shed some light on this ?
I need to vlookup on a filtered range using VBA. I have gone through different threads posted on same topic, however they can't actually answer my question.
Basically the sheet where I need to perform the lookup, will be received via User input. My code goes as follows :
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
When I run the code, most of the time it is not throwing any error and in the file I can see the rows are filtered, however the lookup is not done. Also, there are times when the code is throwing runtime error.
Can anyone please shed some light on this ?
