Using a variable of Type Range in VLookup in VBA

Question

I am trying to populate a range of cells dynamically using VLookup. My Active Sheet is where I am trying to insert value of cells from another worksheet in same workbook. For the second parameter of VLookup I am trying to use the variable dataRng which gets me the entire range of values to lookup in my source sheet (srcSheet). All the code works as expected except the VLookup returns #NAME? and its the dataRng variable which seems to be the issue. Any suggestions?

Sub VlookUpCreateDate()
    Dim srcSheetName As String
    Dim currSheetName As String
    Dim currlastRow As Long
    Dim currlastCol As Long
    Dim srcLastRow As Long
    Dim srcLastCol As Long
    Dim srcFirstRow As Long
    Dim srcSheet As Worksheet
    Dim firstVar As String

    Dim refRng As Range, ref As Range, dataRng As Range

    srcSheetName = ActiveWorkbook.Worksheets(1).Name
    Set srcSheet = ActiveWorkbook.Sheets(srcSheetName)

    'Get Last Row and Column
    With ActiveSheet
        currlastRow = ActiveSheet.UsedRange.Rows.Count
        currlastCol = ActiveSheet.UsedRange.Columns.Count
    End With

    With srcSheet
        srcFirstRow = 2
        srcLastRow = srcSheet.UsedRange.Rows.Count
        srcLastCol = srcSheet.UsedRange.Columns.Count
    End With

    Set dataRng = srcSheet.Range(srcSheet.Cells(srcFirstRow, srcLastCol),  srcSheet.Cells(srcLastRow, srcLastCol))

    For i = 2 To currlastRow
        Cells(i, currlastCol + 1).Select
ConvertToLetter & "$" & srcLastRow
        ActiveCell.Formula = "=VLOOKUP(A2,dataRng,4,False)"
    Next i

End Sub

Davesexcel Davesexcel · Accepted Answer · 2015-08-10T18:47:39

Here is a simple example that should work for you, using the dataRng address in the formula

   Dim dataRng As Range, s
    Set dataRng = Range("AA1:AF7")
    s = dataRng.Address

    ActiveCell = "=VLOOKUP(A2," & s & ",4,0)"

Using a variable of Type Range in VLookup in VBA

1 Answers