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

 

[Fluff]

Try it like

If Rectangle_Height = "Height_3m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_3")
If Rectangle_Height = "Height_4m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_4")

 

[Zot]

Why you are having Table_Fire_Percent twice?
Table_Fire_Percent = Table_Fire_Percent = Worksheets("Tables").Range("Table_3")

 

[thecat23]

Why you are having Table_Fire_Percent twice?
Table_Fire_Percent = Table_Fire_Percent = Worksheets("Tables").Range("Table_3")

Yes, I just found this error as well, problem solved. Stupid mistake. Thanks anyway!

 

[thecat23]

Try it like

If Rectangle_Height = "Height_3m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_3")
If Rectangle_Height = "Height_4m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_4")

Thanks, there was a typo in the code, I have typed twice. :D

 

[Zot]

Yes, I just found this error as well, problem solved. Stupid mistake. Thanks anyway!

Sometimes we just think too much and too complicated to see simple solution ?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Jon Peltier]

You can improve it further by explicitly stating the .Value property. It's probably okay since it's the default, but it's a bad habit:

If Rectangle_Height = "Height_3m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_3").Value
If Rectangle_Height = "Height_4m" Then Table_Fire_Percent = Worksheets("Tables").Range("Table_4").Value

 

[Rick Rothstein]

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

Not sure if you are interested, but you can replace all of the above lines of code with this single line of code...

Col_2 = (2 + InStr("02 03 04 06 08 10 15 20 30", Format(Rectangle_Width, "00"))) / 3

 

[thecat23]

Not sure if you are interested, but you can replace all of the above lines of code with this single line of code...

Col_2 = (2 + InStr("02 03 04 06 08 10 15 20 30", Format(Rectangle_Width, "00"))) / 3

Hi Rick. I just tried your code that I initially don't understand, but it worked!!!. Then I looked up the Instr & format functions, and spent 10 min thinking over it until the light shines. Thanks for enlightening me on this, admire your knowledge.