Functions in VBA

[welshgasman]

Hi all,

I created a function to calculate a value to be returned from a matrix in a sheet.

The function is below

As you can see I have tried to break it down to find where the error lies.
If I use

=IF(C126="Investment",introrate(A126,C126,D126,I126),introrate(A126,C126,F126,I126))

as the formula, it works for some of the options, but where the amount passed in is > 14200 and pstrFeeType = IFA, I get #Value in the sheet, yet if I use
the values of the cells in the immediate window such as

? introrate("CC Fee","IFA",38545.34,"TPA Service")
I get the value 0 which is correct.?

What am I missing please?

Function IntroRate(pstrProdType As String, pstrFeeType As String, pcurAmount As Currency, pstrIntroducer As String)
' This function will return the % payable to an Introducer
Dim strColHeader As String
Dim iRow As Integer, iCol As Integer


If pcurAmount < 14200 Then   
    If pstrFeeType = "IFA" Then
        strColHeader = Sheets("Matrix").Range("B1").Value
    Else
        strColHeader = Sheets("Matrix").Range("C1").Value
    End If
Else
    Select Case pstrProdType
        Case "JL Fee"
            strColHeader = Sheets("Matrix").Range("E1").Value
        Case "JL Invest"
            strColHeader = Sheets("Matrix").Range("F1").Value
        Case "CC Invest"
            strColHeader = Sheets("Matrix").Range("G1").Value
        Case "OT Fee"
            strColHeader = Sheets("Matrix").Range("H1").Value
        Case "CC Fee"
            strColHeader = Sheets("Matrix").Range("D1").Value


    End Select
End If
iRow = WorksheetFunction.Match(pstrIntroducer, Sheets("Matrix").Range("A1:A30"), 0)
iCol = WorksheetFunction.Match(strColHeader, Sheets("Matrix").Range("A1:H1"), 0)
IntroRate = WorksheetFunction.Index(Sheets("Matrix").Range("A1:H30"), iRow, iCol)


    'IntroRate = WorksheetFunction.Index(Sheets("Matrix").Range("A1:H30"), WorksheetFunction.Match(pstrIntroducer, Sheets("Matrix").Range("A1:A30"), 0), WorksheetFunction.Match(strColHeader, Sheets("Matrix").Range("A1:H1"), 0))
    'Debug.Print IntroRate


End Function

 

[welshgasman]

Errant space caused the problem.