VBA 'Vlookup'函数在动态行数上运行

我不确定如何将功能与Sub结合使用 . 最有可能的是,Sub I以下需要更正 .

我在两个单独的工作表中有两个表:Sheet1和Sheet2 .

两个表都有动态行数,但第一行始终在同一个地方开始,两个表中的列数也是不变的 . Sheet1数据从A2开始,以R2结束:R和Sheet2数据从A3开始,以H3:H结束 .

我试图在Sheet1的列O中实现VLOOkUP,它将填充Sheet1的列O中的每个单元格,并在Sheet2中使用列D的相关值 . 到目前为止,我设法提出如下代码 .

Public Function fsVlookup(ByVal pSearch As Range, ByVal pMatrix As Range, ByVal pMatColNum As Integer) As String
    Dim s As String
    On Error Resume Next
    s = Application.WorksheetFunction.VLookup(pSearch, pMatrix, pMatColNum, False)
    If IsError(s) Then
        fsVlookup = ""
    Else
        fsVlookup = s
    End If
End Function
Public Sub Delinquency2()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range
    Dim rCell As Range


    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    pSearch = ws1.Range("D2:D" & Cells(Rows.Count, "A").End(xlDown).Row)
    pMatrix = ws2.Range("$A3:$H" & Cells(Rows.Count, "C").End(xlDown).Row)
    pMatColNum = 4

    Set rng = ws1.Range("O2:O" & Cells(Rows.Count, "A").End(xlDown).Row)

    For Each rCell In rng.Cells
             With rCell
                               rCell.FormulaR1C1 = s

            End With
        Next rCell

End Sub

回答(1)

2 years ago

您需要使用与下面类似的行调用sub中的函数 . 然后它从您的sub获取您的值并将它们输入到函数中并返回该值 .

您需要调整范围,以便在函数中正确识别它们 . 我已经更新了您的代码以使其工作,您可以摆弄它以使其按您希望的方式工作 . 我还更新了一些其他的点以找出正确的范围,你不想在你使用它的地方使用 xlDown ,导致一个巨大的循环覆盖你不想要它的单元格 .

Public Function fsVlookup(ByVal pSearch As Range, ByVal pMatrix As Range, ByVal pMatColNum As Integer) As String
    Dim s As String
    On Error Resume Next
    s = Application.WorksheetFunction.VLookup(pSearch, pMatrix, pMatColNum, False)
    If IsError(s) Then
        fsVlookup = ""
    Else
        fsVlookup = s
    End If
End Function.

Public Sub Delinquency2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range
    Dim rCell As Range, pMatrix As Range

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    pSearchCol = ws1.Range("D2:D2").Column
    Set pMatrix = ws2.Range("$A3:$H" & ws2.Cells(Rows.Count, "C").End(xlUp).Row)
    pMatColNum = 4

    Set rng = ws1.Range("O2:O" & ws1.Cells(Rows.Count, "A").End(xlUp).Row)

    For Each rCell In rng.Cells
         With rCell
             rCell.Value = fsVlookup(ws1.Cells(rCell.Row, pSearchCol), pMatrix, pMatColNum)
        End With
    Next rCell

End Sub