How to use a range in a custom function?

[Danny]

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.

 

[ShaneDevenshire]

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

 

[T. Valko]

Why reinvent the wheel?

Worksheet functions will do this and will be more efficient than a UDF.

=IF(COUNTIF(Sheet2!A$1:A$366,E4),VLOOKUP(E4,Sheet2!A$1:B$366,2,0),"")

 

[Danny]

I inserted this worksheet function and it worked but when I copy it to
another cell it changes the range based on where I copy it to. I dont want to
need to rewrite the function for each block is why I was trying to do it
through a udf. I still get a 0 for the ones that dont have text in column 2
(not an important date)

P.S.
I know I can use worksheet functions in vba that is what I was doing with:
Application.lookup()
I didn't understand what was meant by use Evaluate?
My range is dates in one column, and text in column 2 for only the important
dates.

My main question is how to do range in vba?
regardless of my situation if I were doing in vba:
EXAMPLE
Function name(cell1,cell2,etc)
Application.Sum(a1:a5)
End Function

vba doesn't recognize a1:a5
It says
Compile Error:
Expected: list separator or )
and the ":" is highlighted

 

[T. Valko]

Not sure how you intend to use this but even if you have a UDF you still
need to define the ranges that the function takes as arguments. For example:

=MyLookup(E4,Table)

You can make Table a static/absolute reference but E4 will always change
when you copy the formula to another location.

I'm not sure I understand your requirements. I would ssuggest you post in
the programming forum.

 

[Danny]

Thanks for your help but I figured it out. I looked at another post where
someone used a range on another sheet like I was trying to do by using
Sheets("sheet2").Range("a1:b365")

Function Lookupdate(datehere)
Application.Volatile
If datehere = 0 Then
Lookupdate = ""
Else
Lookupdate = Application.Lookup(datehere, Sheets("Sheet2").Range("a1:b366"))
If Lookupdate = 0 Then
Lookupdate = ""
End If
End If
End Function

Thanks again for your help.