UDF returns #Value! error

thecat23

New Member
Joined
Apr 6, 2019
Messages
9
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,130,031
Messages
5,639,642
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top