excel - 在下一个可见单元格上应用 VLOOKUP

Question

下面的代码执行 vlookup 然后自动填充数据然后将过滤器应用于 #N/A。在这里，我需要在同一列中使用过滤器作为 #N/A 执行另一个 VLOOKUP 但我不确定这一点，因为我们如何选择下面的单元格 F1 并对可见数据应用 VLOOKUP。你能帮我解决这个问题吗？

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

Answer 1

一些事情

1. 避免使用Activate/Select。你可能想看看 How to avoid using Select in Excel VBA

2. 定义和使用对象。使用您的代码变得更加容易。

3. 与其在一个单元格中输入公式然后自动填充，不如一次性在整个范围内输入公式，如下所示。

4. 我看到目标是获取所有值。则无需输入公式、过滤和租用公式。使用使用 IFERROR 和 IF 的单个嵌套公式。例如，如果公式 "=Vlookup(B2,'sales'!B:C,2,0)" 没有给出结果，而您想从 say 列中提取值D 然后使用公式 =IFERROR(VLOOKUP(B2,Sales!B:C,2,0),VLOOKUP(B2,Sales!B:D,3,0))。我只是将 VLOOKUP(B2,Sales!B:D,3,0) 嵌套在 IFERROR() 中。该公式的作用是检查 VLOOKUP(B2,Sales!B:C,2,0) 是否有错误，如果有，则尝试使用 VLOOKUP 查找值(B2,销售!B:D,3,0)

代码

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