excel - 如何在错误值为0的情况下应用vlookup并恢复

Question

我正在使用vlookup范围在最后一个空列中应用公式。
它的工作正常，但如果有错误，则显示空白单元格，嗯，我不在乎，但是，
当在下一列中再次应用该公式时，该值将移至上一列的空白单元格，因为由于错误，上一列中的一个单元格为空白。

因此，我需要它赋予值0或“错误”而不是空白。

Sub GET_BHAV()

    Dim OpenWs As Worksheet, bhavWs As Worksheet
    Dim OpenLastRow As Long, bhavLastRow As Long, x As Long
    Dim bhavRng As Range

    Set OpenWs = ThisWorkbook.Worksheets("Open")

    Workbooks.Open "C:\Users\playt\Desktop\STACK\VANGU\cm07JAN2020bhav.csv"

    Set bhavWs = Workbooks("cm07JAN2020bhav.csv").Worksheets("cm07JAN2020bhav")

    bhavLastRow = bhavWs.Range("A" & Rows.Count).End(xlUp).Row
    OpenLastRow = OpenWs.Range("A" & Rows.Count).End(xlUp).Row

    Set bhavRng = bhavWs.Range("A2:G" & bhavLastRow)

    For x = 2 To OpenLastRow
        On Error Resume Next
        OpenWs.Cells(x, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Application.WorksheetFunction.VLookup( _
                                                                          OpenWs.Range("A" & x).Value, bhavRng, 3, Flase)
    Next x

End Sub

Answer 1

您需要捕获(处理)VLOOKUP错误，以防找不到匹配项。

尝试下面VLOOKUP部分的代码:

' define a new variable
Dim VlookUpVal As Variant

With OpenWs ' Using With statement
    For x = 2 To OpenLastRow
        ' read the value of VLOOKUP to variant
        VlookUpVal = Application.VLookup(.Range("A" & x).Value, bhavRng, 3, False)

        ' check if VLOOKUP returned an Error
        If IsError(VlookUpVal) Then
            .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = 0
        Else
            .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = VlookUpVal
        End If
    Next x
End With