title: Using a variable of Type Range in VLookup in VBA
body: <p>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?</p>
  
  <pre><code>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 &amp; "$" &amp; srcLastRow
          ActiveCell.Formula = "=VLOOKUP(A2,dataRng,4,False)"
      Next i
  
  End Sub
  </code></pre>