PDA

View Full Version : Solved: is there a limit on expressions in IF Conditions in Excel VBA



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

Bob Phillips
09-23-2011, 01:43 AM
Function SUFactor(piecesperpkt, pocketperinner, pocketinnerperctn, UOM)

If Not IsError(Application.Match(UOM, Array(
"PC", "BAG", "BDL", "BK", "BOOK", "BRL", "BTL", _
"BTL", "CAN", "GNY", "JOB", "KG", "M2", "MTR", _
"NO", "NOS", "PAD", "PAIL", "PAIR", "PCS", "ROLL", _
"SACHET", "TIN", "TUB", "UNIT", "YD", "YRD"), 0)) Then

If pocketperinner = 0 Then

SUFactor = piecesperpkt * pocketinnerperctn
Exit Function
ElseIf pocketperinner > 0 Then

SUFactor = piecesperpkt * pocketperinner * pocketinnerperctn
Exit Function
End If

ElseIf Not IsError(Application.Match(UOM, Array("BOX", "DOZ", "PKT", "REAM", "SET", "TUBE"), 0)) Then

If pocketperinner = 0 Then

SUFactor = pocketinnerperctn
Exit Function
ElseIf pocketperinner > 0 Then

SUFactor = pocketperinner * pocketinnerperctn
Exit Function
End If

ElseIf UOM = "CTN" Then

SUFactor = 1
Else

MsgBox "Invalid [UOM]", vbOKOnly + vbCritical
SUFactor = "WRONG VALUE"
End If
End Function

kirbz
09-23-2011, 03:34 AM
Hi Xld,

Thank you very much, it solved my problem.. :thumb