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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
Why you are having Table_Fire_Percent twice?
Table_Fire_Percent = Table_Fire_Percent = Worksheets("Tables").Range("Table_3")
 

thecat23

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

New Member
Joined
Apr 6, 2019
Messages
9

ADVERTISEMENT

Try it like
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,979
Office Version
  1. 2016
Platform
  1. Windows
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...
VBA Code:
Col_2 = (2 + InStr("02 03 04 06 08 10 15 20 30", Format(Rectangle_Width, "00"))) / 3
 
  • Like
Reactions: Zot

thecat23

New Member
Joined
Apr 6, 2019
Messages
9
Not sure if you are interested, but you can replace all of the above lines of code with this single line of code...
VBA 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,030
Messages
5,639,635
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