Apply VLOOKUP on next visible cell

Question

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

What is the **Exact** vlookup formula that you want to apply after the filter. I believe there is a better way to handle this situation — Siddharth Rout, May 17 '21 at 17:41
for starters you can skip the select all together by setting the range value equal to the range you want to paste. Like ```Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row).value = Range("F2").value``` that being said, I'd ask you why you're pasting a formula into a cell with code? Why not directly set the value to the desired results? — Chris H., May 17 '21 at 17:42

score 3 · Answer 1 · edited Jan 07 '22 at 07:07

Few things

Avoid using Activate/Select. You may want to see How to avoid using Select in Excel VBA
Define and work with objects. Becomes much easier to work with your code.
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.
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

Apply VLOOKUP on next visible cell

1 Answers1