UDF Function Not working

J

jlclyde

I have two functions. One to split out each side of a measurement ....
36 1/8 X 40 17/32. This is in one cell. The functions are supposed
to take and find the square inches of the measurements. So in this
case FCArea would be 36.125 * 40.53125. It is finding the right cell
and all of the right information seems to be going to the right
places, then it returns a #value. Any help will be greatly
appreciated.

Thanks,
Jay

Function FCarea() As Double
Application.Volatile
Dim Fnd As String
Dim L As String, R As String, X As Integer
Fnd = Range("B:B").Find(what:="Final Size:").Offset(2, 0).Value
X = InStr(1, Fnd, "X", 1)
L = Left(Fnd, X - 1)
R = Right(Fnd, Len(Fnd) - X)
FCarea = Frac2Num(L) * Frac2Num(R)
End Function

Function Frac2Num(ByVal X As String) As Double
Dim P As Integer, N As Double, Num As Double, Den As Double
X = Trim$(X)
P = InStr(X, "/")

If P = 0 Then
N = Val(X)
Else
Den = Val(Mid$(X, P + 1))

If Den = 0 Then Error 11 ' Divide by zero
X = Trim$(Left$(X, P - 1))
P = InStr(X, " ")
If P = 0 Then
Num = Val(X)
Else
Num = Val(Mid$(X, P + 1))
N = Val(Left$(X, P - 1))
End If
End If
If Den <> 0 Then
N = N + (Num / Den)
End If
Frac2Num = N
End Function
 
S

Sheeloo

There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.
 
J

jlclyde

There is nothing wrong with your formula.

I entered
"Final Size:" in B5,
36 1/8 X 40 17/32 in B7 (because of Offset(2, 0) in the code),
=FCarea() in A1
and got 1464.191406 as the result.








- Show quoted text -

Sheelo,
I am glad that it working for you. I wish it was working for me. Any
thoughts?
Thanks,
Jay
 
S

Sheeloo

I also wish it works foy you.

I have sent my testfile to your email id.

How are you testing? Make sure there are no extra characters in the cell you
are testing it.

Where do you have the code? In a module?
 
J

jlclyde

I also wish it works foy you.

I have sent my testfile to your email id.

How are you testing? Make sure there are no extra characters in the cell you
are testing it.

Where do you have the code? In a module?






- Show quoted text -

I got your file and sent you mine. For somereason all of my other
UDFs work fine, but this one is throwing a value. I have it in a
module with other functions. Your file opens and the UDF works fine.
I ahve no idea why it is no longer working on my file.

Thanks,
Jay
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top