Unlimited Range for VlookUp in Macro Code

[fmfernandez11]

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