Hi,

I'm probably not telling you something you don't already know, but...
1. You can use Excel's built in function on the vba side by calling them with
WorksheetFunction.VLOOKUP(...)
2. You can use the EVALUATE method

trigVariable = [SIN(45)]
trigVariable = Evaluate("SIN(45)")

3. The spreadsheet's ISNA function can trap the N/A errors

--
Thanks,
Shane Devenshire


"Danny" wrote:

I am trying to do a custom lookup function where the range remains constant
a1:b365 and I am trying to lookup a long list where some references are blank
and do not want #NA throughout the document so I tried:

Function Lookupdate(datehere)
'
If datehere = 0 Then
Lookupdate = ""
Else
If datehere < 0 Then {is < not equal to?}
Lookupdate = Application.Lookup(datehere, Sheet2!Range(A1:b366))

End If
End If
End Function

where datehere is a date on a calendar and I am trying to lookup that date
in column a on sheet 2 and return column b (importance of date - holiday,
birthday, appointment,etc)from sheet 2.
lookup(e4,sheet2!a1:b366) works in excel but when trying to do this in a
custom function with vba I cannot get it to work.
I need someway to do this for a long list with out getting #N/A and also
when I try to autofill the lookup function in excel it changes the range also.