Solved: nightmare fractions in formulas

[vassili]

i'm having a hell of a time with this form i have to automate.

in the unit column there is a mix of numbers, words, and fractions (as text).

in the total units column i need to add a formula. for the time being, i'll just have it as 5 multiplied by whatever is in the unit column.

in the attachment you can see what i have to deal with. what do i have to do in vba to make the final output make sense? and how can i change the output #VALUE! to a custom message?

obviously when i multiply 5 by the words PCL or AR i'm gonna get #VALUE!, but i want it to say something else, like hello. how can this be achieved?

remember, everything is automated. i pull in the spreadsheet form through my macro program and i add the "total unit" column on after with a vba loop. what the attachment shows is the final output of my macro.

[Bob Phillips]

=IF(ISNUMBER(BD9),5*BD9,"hello ****")

[mdmackillop]

Here's a user defined function (UDF) to assist.
Enter the code in a standard module.
In cell BM9 enter =F(BD9) for the value or =F(BD9,5) to multiply by 5. Note that =F(BD9)*5 will NOT work due to text values.
[vba]Option Explicit
Function F(Data As Range, Optional Times As Long)
Dim Num, Den
If Times = 0 Then Times = 1
If InStr(1, Data, "/") = 0 Then
If IsNumeric(Data) Then
F = Data * Times
Else
F = "Hello " & Data
End If
Else
Num = Split(Data, "/")(0)
Den = Split(Data, "/")(1)
If IsNumeric(Num) And IsNumeric(Den) Then
F = (Num / Den) * Times
Else
F = "Hello " & Data
End If
End If
End Function

[/vba]

[vassili]

Here's a user defined function (UDF) to assist.
Enter the code in a standard module.
In cell BM9 enter =F(BD9) for the value or =F(BD9,5) to multiply by 5. Note that =F(BD9)*5 will NOT work due to text values.
[vba]Option Explicit
Function F(Data As Range, Optional Times As Long)
Dim Num, Den
If Times = 0 Then Times = 1
If InStr(1, Data, "/") = 0 Then
If IsNumeric(Data) Then
F = Data * Times
Else
F = "Hello " & Data
End If
Else
Num = Split(Data, "/")(0)
Den = Split(Data, "/")(1)
If IsNumeric(Num) And IsNumeric(Den) Then
F = (Num / Den) * Times
Else
F = "Hello " & Data
End If
End If
End Function

[/vba]

thanks man, works great!