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.

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.

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.

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.

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.

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.