Apply VLOOKUP on next visible cell

user_13346246 0
Viewed 82

The below code does vlookup then autofills the data then applies the filter to #N/A. Here I need to do another VLOOKUP in the same column with the filter as #N/A but I am not sure about this as how do we select the cells below F1 and apply VLOOKUP on the visible data. Could you help me out with this?

Sub Vlookup()
    Worksheets("error rate").Activate
    Range("F2") = "=Vlookup(B2,'sales'!B:C,2,0)"
    Range("F2").Select
    Range("F2").AutoFill Range("F2:F" & Range("B" & Rows.Count).End(xlUp).Row)
    Range("F:F").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Range("B1").AutoFilter Field:=6, Criteria1:="#N/A"
    Range = Rng.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6)
End Sub
vba excel
1 Answers

Few things

  1. Avoid using Activate/Select. You may want to see How to avoid using Select in Excel VBA

  2. Define and work with objects. Becomes much easier to work with your code.

  3. Instead of entering formula in one cell and then autofilling it, simply enter the formula in the entire range in one go as shown below.

  4. I see the objective is to get all the values. Then there is no need to enter a formula, filter and renter the formula. Use a single nested formula using IFERROR and IF. For example, if the formula "=Vlookup(B2,'sales'!B:C,2,0)" doesn't give you the result and you want to pull up the values from say column D then use the formula =IFERROR(VLOOKUP(B2,Sales!B:C,2,0),VLOOKUP(B2,Sales!B:D,3,0)). I have simply nested VLOOKUP(B2,Sales!B:D,3,0) inside IFERROR(). What the formula does is checks if there is an error with VLOOKUP(B2,Sales!B:C,2,0) and if there is, then it attempts to find the value using VLOOKUP(B2,Sales!B:D,3,0)

CODE

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    
    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("error rate")
    
    With ws
        '~~> Find last row
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        
        '~~> Work with the relevant range
        With .Range("F2:F" & lRow)
            '~~> Enter the formula in the entire range in one go
            .Formula = "=IFERROR(VLOOKUP(B2,Sales!B:C,2,0),VLOOKUP(B2,Sales!B:D,3,0))"
            
            '~~> OPTIONAL
            '~~> Instead of copy and paste as values use this.
            '.Value = .Value
        End With
    End With
End Sub
user_11405790
Related