PDA

View Full Version : Solved: nightmare fractions in formulas



vassili
06-14-2007, 03:32 AM
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
06-14-2007, 04:28 AM
=IF(ISNUMBER(BD9),5*BD9,"hello ****")

mdmackillop
06-14-2007, 05:00 AM
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.
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

vassili
06-17-2007, 09:04 PM
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.
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



thanks man, works great!