UDF returns #Value! error

[thecat23]

Hi guys

I am trying to write a function myself to retrieve data from few tables using the index function. The function will look up certain rows, columns, from different tables based on the parameters defined in the function. I have started simple with one table to begin with, the function is working just fine and returns the data (numbers) that I wanted. However, when I added an additional parameter highlighted in RED. This "Rectangle_Height" parameter is an string, which is going to determine which table it's going to look up based on the input cell. But I receive #Value! error, the warning message say the value used in the formula is of the wrong type?

I couldn't figure out why after few hours of researching on line. Can any one help on this one please? Thanks!

Regards

--------------------------------------------------------------------------
Option Explicit

Public Function get_Max_Percent_FRR (FLED As String, Rectangle_Width As Double, Rectangle_Height As String)

Dim Col_1 As Double
Dim Col_2 As Double
Dim Row As Double
Dim Table_Fire_Percent As Variant

Row = 3

'Returns #Value! error as long as I put the if Rectangle_Height = "Height_3m" conditions below, otherwise it works perfectly.
If Rectangle_Height = "Height_3m" Then Table_Fire_Percent = Table_Fire_Percent = Worksheets("Tables").Range("Table_3")
If Rectangle_Height = "Height_4m" Then Table_Fire_Percent = Table_Fire_Percent = Worksheets("Tables").Range("Table_4")


If FLED = "FLED_Less_400" Then Col_1 = 1
If FLED = "FLED_400_800" Then Col_1 = 9
If FLED = "FLED_More_800" Then Col_1 = 17

If Rectangle_Width = 2 Then Col_2 = 1
If Rectangle_Width = 3 Then Col_2 = 2
If Rectangle_Width = 4 Then Col_2 = 3
If Rectangle_Width = 6 Then Col_2 = 4
If Rectangle_Width = 8 Then Col_2 = 5
If Rectangle_Width = 10 Then Col_2 = 6
If Rectangle_Width = 15 Then Col_2 = 7
If Rectangle_Width = 20 Then Col_2 = 8
If Rectangle_Width = 30 Then Col_2 = 9

get_Max_Percent_FRR = WorksheetFunction.Index(Table_Fire_Percent, Row, Col_1 + Col_2)

End Function

 

[Rick Rothstein]

You cannot know how happy I was when I read your response... unfortunately it is very rare to hear that a poster actually dug through the code I posted in order to understand how it works.