kirbz
09-23-2011, 12:48 AM
Helo fellow vbaxer's
Need your help, below is my Excel VBA Code. but it has a problem, I'm not sure if i have something wrong in my code, or there is a limit using too many OR expression on my IF Condtion.. as the result of my Code show #VALUE!.. when i put the UOM value..
Function SUFactor(piecesperpkt, pocketperinner, pocketinnerperctn, UOM)
If UOM = "PC" Or UOM = "BAG" Or UOM = "BDL" Or UOM = "BK" Or UOM = "BOOK" Or UOM = BRL Or UOM = "BTL" Or UOM = "BTL" Or UOM = "CAN" Or UOM = "GNY" Or UOM = "JOB" Or UOM = "KG" Or UOM = "M2" Or UOM = "MTR" Or UOM = "NO" Or UOM = "NOS" Or UOM = "PAD" Or UOM = "PAIL" Or UOM = "PAIR" Or UOM = "PCS" Or UOM = "ROLL" Or UOM = "SACHET" Or UOM = "TIN" Or UOM = "TUB" Or UOM = "UNIT" Or UOM = "YD" Or UOM = "YRD" And pocketperinner = 0 Then
SUFactor = piecesperpkt * pocketinnerperctn
ElseIf "PC" Or UOM = "BAG" Or UOM = "BDL" Or UOM = "BK" Or UOM = "BOOK" Or UOM = BRL Or UOM = "BTL" Or UOM = "BTL" Or UOM = "CAN" Or UOM = "GNY" Or UOM = "JOB" Or UOM = "KG" Or UOM = "M2" Or UOM = "MTR" Or UOM = "NO" Or UOM = "NOS" Or UOM = "PAD" Or UOM = "PAIL" Or UOM = "PAIR" Or UOM = "PCS" Or UOM = "ROLL" Or UOM = "SACHET" Or UOM = "TIN" Or UOM = "TUB" Or UOM = "UNIT" Or UOM = "YD" Or UOM = "YRD" And pocketperinner > 0 Then
SUFactor = piecesperpkt * pocketperinner * pocketinnerperctn
ElseIf UOM = "BOX" Or UOM = "DOZ" Or UOM = "PKT" Or UOM = "REAM" Or UOM = "SET" Or UOM = "TUBE" And pocketperinner = 0 Then
SUFactor = pocketinnerperctn
ElseIf UOM = "BOX" Or UOM = "DOZ" Or UOM = "PKT" Or UOM = "REAM" Or UOM = "SET" Or UOM = "TUBE" And pocketperinner > 0 Then
SUFactor = pocketperinner * pocketinnerperctn
ElseIf UOM = "CTN" Then
SUFactor = 1
Else
ReminderMsg = MsgBox("Invalid [UOM]", vbOKOnly + vbCritical)
SUFactor = "WRONG VALUE"
End If
End Function
Need your help, below is my Excel VBA Code. but it has a problem, I'm not sure if i have something wrong in my code, or there is a limit using too many OR expression on my IF Condtion.. as the result of my Code show #VALUE!.. when i put the UOM value..
Function SUFactor(piecesperpkt, pocketperinner, pocketinnerperctn, UOM)
If UOM = "PC" Or UOM = "BAG" Or UOM = "BDL" Or UOM = "BK" Or UOM = "BOOK" Or UOM = BRL Or UOM = "BTL" Or UOM = "BTL" Or UOM = "CAN" Or UOM = "GNY" Or UOM = "JOB" Or UOM = "KG" Or UOM = "M2" Or UOM = "MTR" Or UOM = "NO" Or UOM = "NOS" Or UOM = "PAD" Or UOM = "PAIL" Or UOM = "PAIR" Or UOM = "PCS" Or UOM = "ROLL" Or UOM = "SACHET" Or UOM = "TIN" Or UOM = "TUB" Or UOM = "UNIT" Or UOM = "YD" Or UOM = "YRD" And pocketperinner = 0 Then
SUFactor = piecesperpkt * pocketinnerperctn
ElseIf "PC" Or UOM = "BAG" Or UOM = "BDL" Or UOM = "BK" Or UOM = "BOOK" Or UOM = BRL Or UOM = "BTL" Or UOM = "BTL" Or UOM = "CAN" Or UOM = "GNY" Or UOM = "JOB" Or UOM = "KG" Or UOM = "M2" Or UOM = "MTR" Or UOM = "NO" Or UOM = "NOS" Or UOM = "PAD" Or UOM = "PAIL" Or UOM = "PAIR" Or UOM = "PCS" Or UOM = "ROLL" Or UOM = "SACHET" Or UOM = "TIN" Or UOM = "TUB" Or UOM = "UNIT" Or UOM = "YD" Or UOM = "YRD" And pocketperinner > 0 Then
SUFactor = piecesperpkt * pocketperinner * pocketinnerperctn
ElseIf UOM = "BOX" Or UOM = "DOZ" Or UOM = "PKT" Or UOM = "REAM" Or UOM = "SET" Or UOM = "TUBE" And pocketperinner = 0 Then
SUFactor = pocketinnerperctn
ElseIf UOM = "BOX" Or UOM = "DOZ" Or UOM = "PKT" Or UOM = "REAM" Or UOM = "SET" Or UOM = "TUBE" And pocketperinner > 0 Then
SUFactor = pocketperinner * pocketinnerperctn
ElseIf UOM = "CTN" Then
SUFactor = 1
Else
ReminderMsg = MsgBox("Invalid [UOM]", vbOKOnly + vbCritical)
SUFactor = "WRONG VALUE"
End If
End Function