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.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Danny" wrote in message
...
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" wrote:

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),"")

--
Biff
Microsoft Excel MVP


"Danny" wrote in message
...
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.