Unlimited Range for VlookUp in Macro Code

I am trying to program a Vlookup for Column C, however the macro will be used for more than one report. Can anyone show me how to program the range to be unlimited? When I put C655636 (the max amount of rows for Excel 2003), Excel freezes and will not respond. This is one part of a very large macro but I can not continue until this is solved.

Your help is appreciated!

Thanks!

Sub ConductVlookupCopyOver()
'
' ConductVlookupCopyOver Macro
' Macro recorded 1/4/2013 by cac1057
'

'
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Previous Hold Transfer Report.XLS]HoldTransfer Over & Under 25 De'!C2,1,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C943")
    Range("C2:C943").Select
End Sub

Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Previous Hold Transfer Report.XLS]HoldTransfer Over & Under 25 De'!C2,1,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2")
    Range("C2").Select

The error message appears in the

 Selection.AutoFill Destination:=Range("C2")

I have changed the range to "C2:C" and I have also changed the formulas to the ones given above by bstubbs. I recently tried this as well :

    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Previous Hold Transfer Report.XLS]HoldTransfer Over & Under 25 De'!C2,1,FALSE)"
    Range("C2").Select
    lastRow = Range("C2").End(xlDown).Row
    Range("C2").AutoFill Destination:=Range(Range("C2" & lastRow))
    Range("C2").Select

but it did not work. Im in a desperate need of help.

We would love to continue to help you with your query, but first, before we can proceed, please see Forum Rule #3 about code tags and adjust accordingly...

Try this: The LastRow can be either the lastrow used in a particular column, OR it can be the last row used in your spreadsheet (but only one; you'll need to delete the line you DON'T want to use). After determining your LastRow, the VLookup is inserted in each cell in column C beginning with C2 to C and your LastRow.

Sub ConductVlookupCopyOver() '
Dim LastRow As Long
'*********Choose ONLY one of these two options*********
'for last row in a particular column
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'for last row used in the worksheet
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
     SearchDirection:=xlPrevious, LookIn:=xlValues).Row
'*********Choose one of these two options*********

    Selection.Insert Shift:=xlToRight
    Range("C2:C" & LastRow).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[Previous Hold Transfer Report.XLS]HoldTransfer Over & Under 25 De'!C2,1,FALSE)"
    
End Sub