Creating a Function
 

I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

My function needs to look something like;
RATE1 ( €œIN€� or €œOUT€�, €œEE€� or €œER€�, lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%

Creating a Function
 

You have a reply to your previous posting.

"Stu Gnu" wrote:

I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

My function needs to look something like;
RATE1 ( €œIN€� or €œOUT€�, €œEE€� or €œER€�, lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%

Creating a Function
 

Let's assume the following:

Your table is on a worksheet called "LookUpTable"
OUT:EE, etc is in Column 1
Year is in Row 1

Press CTRL F11
Insert a module.
Paste this into that module


Function RATE1(INorOUTString As String, EEorERString As String, LookUpYear
As Long) As Variant
Dim myRow As Long
Dim myCol As Long
Dim myWS As Worksheet
Dim myString As String

On Error Resume Next
Set myWS = ThisWorkbook.Sheets("LookUpTable")
On Error GoTo 0
If myWS Is Nothing Then
RATE1 = "No Lookup Table"
Else
myString = INorOUTString & ":" & EEorERString
Debug.Print myString
myRow = WorksheetFunction.Match(myString, myWS.Range("A:A")) '<~~change
row as needed
Debug.Print myRow
myCol = WorksheetFunction.Match(LookUpYear, myWS.Range("1:1"))
'<~~change col as needed
If myRow 0 And myCol 0 Then
RATE1 = myWS.Cells(myRow, myCol).Value
Else
RATE1 = "No matching row or column"
End If
End If

End Function

Enter something like this in a cell to get the data you need.

=rate1("IN","EE",1992)

--
HTH,
Barb Reinhardt



"Stu Gnu" wrote:

I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

My function needs to look something like;
RATE1 ( €œIN€� or €œOUT€�, €œEE€� or €œER€�, lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%

Creating a Function
 

In a standard module:

Public Function RATE1(myArray As Excel.Range, s1 As String, s2 As
String, myDate As Long) As Double
With Application.WorksheetFunction
RATE1 = myArray.Cells(.Match(s1 & ":" & s2,
myArray.Columns(1), 0), _
.Match(myDate, myArray.Rows(1), 0))
End With
End Function

Usage: =RATE1(A1:F5,"OUT","EE",1992)

A1:F5 is your data range (the year and IN/OUT EE/ER stuff). It uses
the first row of the range to look for year, and the first column of
the range to look for IN/OUT and EE/ER.


On Aug 30, 8:30 am, Stu Gnu wrote:
I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

My function needs to look something like;
RATE1 ( "IN" or "OUT", "EE" or "ER", lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%

Creating a Function
 

Barb

many thanks for your help. I am having a little trouble getting it to run,
but I think it's me rather than your code. I will come back to you (if I
may), if I need a little more help.

Stuart

"Barb Reinhardt" wrote:

Let's assume the following:

Your table is on a worksheet called "LookUpTable"
OUT:EE, etc is in Column 1
Year is in Row 1

Press CTRL F11
Insert a module.
Paste this into that module


Function RATE1(INorOUTString As String, EEorERString As String, LookUpYear
As Long) As Variant
Dim myRow As Long
Dim myCol As Long
Dim myWS As Worksheet
Dim myString As String

On Error Resume Next
Set myWS = ThisWorkbook.Sheets("LookUpTable")
On Error GoTo 0
If myWS Is Nothing Then
RATE1 = "No Lookup Table"
Else
myString = INorOUTString & ":" & EEorERString
Debug.Print myString
myRow = WorksheetFunction.Match(myString, myWS.Range("A:A")) '<~~change
row as needed
Debug.Print myRow
myCol = WorksheetFunction.Match(LookUpYear, myWS.Range("1:1"))
'<~~change col as needed
If myRow 0 And myCol 0 Then
RATE1 = myWS.Cells(myRow, myCol).Value
Else
RATE1 = "No matching row or column"
End If
End If

End Function

Enter something like this in a cell to get the data you need.

=rate1("IN","EE",1992)

--
HTH,
Barb Reinhardt



"Stu Gnu" wrote:

I need to create a custom function that will select a rate from an array
(named NIRATES), based on three crireria. I mistakenly posted this to the
"Worksheet Functions" forum earlier, but it is a custom function I need. Can
somebody help with the code please.

My function needs to look something like;
RATE1 ( €œIN€� or €œOUT€�, €œEE€� or €œER€�, lookupdate)

Lookupdate is formatted as YEAR only.

The table looks is held as follows:
Year 1992 1993 1994 1995 1996
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20%
OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20%
IN:EE 9.00% 9.00% 10.00% 10.00% 10.00%
IN:ER 10.40% 10.40% 10.20% 10.20% 10.20%