0

I have a two sheets one sheet have a data another sheet have a reference supplier list. I need to vlookup with specific suppliers. First i filter the suppliers. After that i need to perform Vlookup funciton. Please if any once help me on this.

Sub Filter1()

Dim DataRK As Worksheet, Ref As Worksheet
Dim RLastRow As Long, DLastRow As Long, X As Long
Dim DataRng As Range, DataRng1 As Range, First As Range
Dim Match

  Application.ScreenUpdating = False

 Set DataRK = ThisWorkbook.Worksheets("Open")
 Set Ref = ThisWorkbook.Worksheets("Reference")

 DLastRow = DataRK.Range("G2:G" & Rows.Count).End(xlDown).Row
 RLastRow = Ref.Range("N" & Rows.Count).End(xlDown).Row
 
Set DataRng = DataRK.Range("A1:T" & DLastRow)

Sheets("Open").AutoFilterMode = False

DataRng.AutoFilter Field:=5, Criteria1:="=*Freight Invoice*", Operator:=xlAnd
        
   Set DataRng1 = Ref.Range("N2:O" & RLastRow&)

 For X = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 18).Select To DLastRow

     ***Match = Application.Vlookup(DataRK.Range("G" & X).Value, DataRng1, 1, False)*** 
 Error is here  above line are not passed please any one help me out
     
        If Not IsError(Match) Then
     DataRK.Range("R" & X).Value =Application.Vlookup(DataRK.Range("G" & X).Value, DataRng1, 1, False)
                        
   
       Else
            With DataRK.Range("R" & X)
            .Value = "Open"
            '.Interior.Color = vbRed
            End With
        End If
Next X

End Sub

Roshan Rajan
  • 5
  • 5
  • You can use Application.WorksheetFunction.VLookup(...) in VBA. But I don't understand what you are doing. VLOOKUP does not need filtered data, it will ignore the filter, but the data should be sorted. – Chris Oct 28 '21 at 12:17
  • How to identify the first row in the filter data please let me know – Roshan Rajan Oct 28 '21 at 12:33
  • Instead of filtering the suppliers first, apply the Vlookup function to all the suppliers, then filter the suppliers that you don't want and clear the contents for these cells. – Elio Fernandes Oct 28 '21 at 13:29
  • I re updated my code with filtering and vlookup some how i build it. where i am stuck is I mentioned in the code. Please any one help me on this!! – Roshan Rajan Oct 28 '21 at 17:21
  • Why do you need to filter? – norie Oct 28 '21 at 17:25
  • @norie I Need to apply Vlookup only for "Freight Invoice". Other value should not change. – Roshan Rajan Oct 29 '21 at 16:24

0 Answers0