setting ranges and vlookup in custom functions
 

Hi,

below is a piece of code of a custom function I worked on:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = (Day(maturity) + 3) & "/" & Month(maturity) & "/" &
Year(maturity)
Else
adj_maturity = (Day(maturity) + 1) & "/" & Month(maturity) & "/" &
Year(maturity)
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.WorksheetFunction.VLookup(adj_maturity , lookuprng, 2, False)
Futures_unr = a
End Function

so the input is a date and output should be a numerical value. I get an
error while, I think, trying to set up a range for vlookup. Does any have any
idea what is causing an error? (vlookup in excel function works fine) Thank
you in advance.

regards,
S

setting ranges and vlookup in custom functions
 

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = maturity + 3
Else
adj_maturity = maturity + 1
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.VLookup(CLng(adj_maturity), lookuprng, 2, False)
Futures_unr = a
End Function

--
__________________________________
HTH

Bob

"SPavlyuk" wrote in message
...
Hi,

below is a piece of code of a custom function I worked on:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = (Day(maturity) + 3) & "/" & Month(maturity) & "/" &
Year(maturity)
Else
adj_maturity = (Day(maturity) + 1) & "/" & Month(maturity) & "/" &
Year(maturity)
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.WorksheetFunction.VLookup(adj_maturity , lookuprng, 2,
False)
Futures_unr = a
End Function

so the input is a date and output should be a numerical value. I get an
error while, I think, trying to set up a range for vlookup. Does any have
any
idea what is causing an error? (vlookup in excel function works fine)
Thank
you in advance.

regards,
S

setting ranges and vlookup in custom functions
 

Bob, thanks!

so I understand I did a mistake in handling the date value... it works well
now. thanks again.

regards,
Sergiy

"Bob Phillips" wrote:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = maturity + 3
Else
adj_maturity = maturity + 1
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.VLookup(CLng(adj_maturity), lookuprng, 2, False)
Futures_unr = a
End Function

--
__________________________________
HTH

Bob

"SPavlyuk" wrote in message
...
Hi,

below is a piece of code of a custom function I worked on:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = (Day(maturity) + 3) & "/" & Month(maturity) & "/" &
Year(maturity)
Else
adj_maturity = (Day(maturity) + 1) & "/" & Month(maturity) & "/" &
Year(maturity)
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.WorksheetFunction.VLookup(adj_maturity , lookuprng, 2,
False)
Futures_unr = a
End Function

so the input is a date and output should be a numerical value. I get an
error while, I think, trying to set up a range for vlookup. Does any have
any
idea what is causing an error? (vlookup in excel function works fine)
Thank
you in advance.

regards,
S

setting ranges and vlookup in custom functions
 

Yeah, the thing to remember is that however it looks, a date is just a
number under the covers (the number of days since Jan 01 1900), so just work
with numbers.

--
__________________________________
HTH

Bob

"SPavlyuk" wrote in message
...
Bob, thanks!

so I understand I did a mistake in handling the date value... it works
well
now. thanks again.

regards,
Sergiy

"Bob Phillips" wrote:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = maturity + 3
Else
adj_maturity = maturity + 1
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.VLookup(CLng(adj_maturity), lookuprng, 2, False)
Futures_unr = a
End Function

--
__________________________________
HTH

Bob

"SPavlyuk" wrote in message
...
Hi,

below is a piece of code of a custom function I worked on:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = (Day(maturity) + 3) & "/" & Month(maturity) & "/" &
Year(maturity)
Else
adj_maturity = (Day(maturity) + 1) & "/" & Month(maturity) & "/" &
Year(maturity)
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.WorksheetFunction.VLookup(adj_maturity , lookuprng, 2,
False)
Futures_unr = a
End Function

so the input is a date and output should be a numerical value. I get an
error while, I think, trying to set up a range for vlookup. Does any
have
any
idea what is causing an error? (vlookup in excel function works fine)
Thank
you in advance.

regards,
S