VLOOUP
 

When I try to run this macro I pop box says, "Excel cannot complete
this task with available resources. Choose less data". What am I doing
wrong?


ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE)=TRUE),"""", VLOOKUP(RC[-5],'Fall 2006 Cohort'!
R2C1:R65536C28,13,FALSE))"
ActiveCell.Offset(0, 1).Select

Thanks in advance.

VLOOUP
 

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).
--
Regards,
Bill Renaud