The part of the VLOOKUP function that refers to the lookup table refers
to R2C1:R65536C28. This is 1.8 million + cells of data. Do you really
have a lookup table that is that large???
You probably should find a way to set a range variable to just the
actual data area. Assuming that the data is in a list, where row 1 is
the column labels, then the following technique would locate the data
area first, then build the VLOOKUP formula, and finally build the entire
formula:
Also, I don't think you need the "=TRUE" part in your formula where you
are checking to see if the result of the VLOOKUP function ISNA.
You had something of the form:
"=IF(ISNA(VLOOKUP(...)=TRUE,"""",VLOOKUP(...))"
It should be:
"=IF(ISNA(VLOOKUP(...),"""",VLOOKUP(...))"
Dim wsData As Worksheet
Dim rngDataArea As Range
Dim strVLookupFormula As String
Dim strFormula As String
Set wsData = Worksheets("Fall 2006 Cohort")
With wsData.UsedRange
'Actual data area is the used range less the column labels.
Set rngDataArea = .Offset(1).Resize(.Rows.Count - 1)
End With
strVLookupFormula = "VLOOKUP(RC[-5]," _
& rngDataArea.Address(ReferenceStyle:=xlR1C1, _
External:=True) & ",13,FALSE)"
strFormula = "=IF(ISNA(" & strVLookupFormula _
& "),""""," & strVLookupFormula & ")"
ActiveCell.FormulaR1C1 = strFormula
ActiveCell.Offset(0, 1).Select
Also, you should probably check what column the ActiveCell is in before
running this macro, as an error will result if it is less than 5 columns
from the left side of the worksheet (the "RC[-5]" part of the VLOOKUP
formula).
