I want to apply the Vlookup function on a column, being the table_array from another sheet in my workbook which is dynamic.
My code works but it's not giving back any values.
Consider only the part in bold of the code:
Private Sub CommandButton3_Click()
Dim src As Worksheet
Dim tgt As Worksheet
Dim com As Worksheet
Dim filterRange As Range
Dim copyRange As Range
Dim copyRange2 As Range
Dim copyRange3 As Range
Dim calcRange As Range
Dim Lastrow As Long
Set src = ThisWorkbook.Sheets("Importação")
Set tgt = ThisWorkbook.Sheets("Status")
Set com = ThisWorkbook.Sheets("Ficheiro Endesa")
src.AutoFilterMode = False
Lastrow = src.Range("B" & src.Rows.Count).End(xlUp).row
Set filterRange = src.Range("B9:N" & Lastrow)
Set copyRange = src.Range("B10:C" & Lastrow)
Set copyRange2 = src.Range("G10:G" & Lastrow)
Set copyRange3 = src.Range("K10:K" & Lastrow)
filterRange.AutoFilter Field:=5, Criteria1:="="
copyRange.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("B21").PasteSpecial xlPasteValues
copyRange2.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("D21").PasteSpecial xlPasteValues
copyRange3.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("E21").PasteSpecial xlPasteValues
filterRange.AutoFilter Field:=5
Lastrow = com.Range("B" & com.Rows.Count).End(xlUp).row
Set calcRange = com.Range("D8:G" & Lastrow)
Range("G21").Select
ActiveCell.FormulaR1C1 = _
"=+IFERROR(VLOOKUP(RC[-5],calcRange,3,FALSE),""-"")"
Range("G21").Select
Range("G21").AutoFill Destination:=Range("G21:G" & Cells(Rows.Count, "B").End(xlUp).row)
End Sub
Bookmarks