if ...then ... else command help vba

[sarveshspace]

I am creating a userform where the user will insert the short diamension , medium diamesion , and longest diamension of a box and the userform will tell if it is an elongated box , flat box or a standard box .

The criterias are as follows -

Flat package -
shortest diamension less than or equal to 8
AND medium diamension is four or more times larger than shortest diamension
AND the volume is equal to or more than 800

elongated package -
longest diamension is 36 or mre
AND both other diamensions are 20 percent or less of the longest diamension

If package qualifies as both flat and elongated , then following message showed show " Package qualifies as both elongated and flat. Test in accordance with elongated procedure ".

If neither are satisfied then its a standard package .

My code is as below .However, it only returns the msg as " it is elongated" .

thanks a lot for your help!







Sub test()
Dim longest As Single
longest = Me.TextBox1.Value
Dim medium As Single
medium = Me.TextBox2.Value
Dim shortest As Single
shortest = Me.TextBox3.Value




'elongated criteria


Dim half1 As Single
half1 = 0.2 * shortest
Dim half2 As Single
half2 = 0.2 * medium




'flat criteria
Dim short1 As Single
short1 = 4 * shortest
Dim volume As Single
volume = longest * shortest * medium






If longest >= 36 & shortest <= half1 & medium <= half2 & shortest <= 8 & medium >= short1 & volume >= 800 Then
MsgBox "Package qualifies as both elongated and flat. Test in accordance with elongated procedure"


ElseIf longest >= 36 & shortest <= half1 & medium <= half2 Then
MsgBox "it is elongated"


ElseIf shortest <= 8 & medium >= short1 & volume >= 800 Then
MsgBox "It is flat"
Else
MsgBox " it is standard"




End If


End Sub

[gmaxey]

Start by replacing your "&" with And in the condition statements.

[Paul_Hossler]

My 2 cents --

1. Instead of trying to determine the answer in a single statement, you could use intermediate variables to hold intermediate results

2. I'd try to have the macro follow your logic rules more closely

3. I just plugged in sizes to test the algorithm, which is much easier to do in a single bite (byte ?) size macro instead of the full blown application

4. The code can be made more concise, but IMHO at the expense of clarity





Option Explicit


'Flat package -
' shortest diamension less than or equal to 8
' AND medium diamension is four or more times larger than shortest diamension
' AND the volume is equal to or more than 800
'Elongated package -
' Longest diamension Is 36 Or more
' AND both other diamensions are 20 percent or less of the longest diamension
' If package qualifies as both flat and elongated, then
' following message showed show "Package qualifies as both elongated and flat. Test in accordance with elongated procedure ".
'If neither are satisfied then its a standard package

Sub test()
Dim Longest As Double, Medium As Double, Shortest As Double, Volume As Double
Dim bFlat As Boolean, bElongated As Boolean

'algorithm testing
Longest = 30
Medium = 10
Shortest = 4


Volume = Longest * Shortest * Medium

If (Shortest <= 8) And (4 * Shortest <= Medium) And (Volume >= 800) Then
bFlat = True
Else
bFlat = False
End If

If (Longest >= 36) And (Medium <= 0.2 * Longest) And (Shortest <= 0.2 * Longest) Then
bElongated = True
Else
bElongated = False
End If

If bFlat And bElongated Then
MsgBox "Package qualifies as both elongated and flat. Test in accordance with elongated procedure"

ElseIf bFlat Then
MsgBox "it is flat"

ElseIf bElongated Then
MsgBox "It is elongated"

Else
MsgBox " it is standard"
End If

End Sub

[sarveshspace]

thank you for your help! however, something is wrong with the code i am using below to decide if type a ,type b or type c

type A = less than 50 lbs and girth equal to or less than 165. Girth is defined as length+2(width+height)
Type B =50 to less than 100 lbs and girth equal to or less than 165
type c = 100 lbs or greater and girth equal to or less than 165

l am gonna use the numbers such that i get type C. So lets say weight is 100 lbs , length is 100 , bread is 20 and height is 20 . So girth is 180. This should qualify for C type . However, on running the code , I get B type. What am i doing wrong?
thanks!


ub parceldelivery1()Dim weight As Double, lnth As Double, width As Double, height As Double, girth As Double
Dim typea As Boolean, typeb As Boolean, typec As Boolean
weight = Me.TextBox31.Value
lnth = Me.TextBox32.Value
width = Me.TextBox33.Value
height = Me.TextBox34.Value

girth = lnth + (2 * (width + height))


If (weight < 50) And (girth <= 165) Then
typea = True
Else
typea = False
End If

If (50 <= weight < 100) And (girth <= 165) Then
typeb = True
Else
typeb = False
End If

If (100 <= weight) And (girth <= 165) Then
typec = True
Else
typec = False
End If

If typea Then
Dim pdA As Range
Set pdA = ActiveDocument.Bookmarks("t1").Range
pdA.Text = "A"


ElseIf typeb Then
Dim pdb As Range
Set pdb = ActiveDocument.Bookmarks("t1").Range
pdb.Text = "B"
Else
If typec Then
Dim pdc As Range
Set pdc = ActiveDocument.Bookmarks("t1").Range
pdc.Text = "C"
End If
End If


End Sub

[Paul_Hossler]

1. I'd change the <<<<<< marked line. I'm not sure but VBA might interpret that as (50 <= Width) as = True and (True < 100)

2. All three tests have (Grith <= 165) so I'm not surprised that Grith = 180 fails





Option Explicit
Sub ParcelDelivery1()
Dim Weight As Double, Lnth As Double, Width As Double, Height As Double, Girth As Double

Dim TypeA As Boolean, TypeB As Boolean, TypeC As Boolean

'l am gonna use the numbers such that i get type C.
'So lets say weight is 100 lbs , length is 100 , bread is 20 and height is 20 .
'So girth is 180. This should qualify for C type .
'However, on running the code , I get B type. What am i doing wrong?


Weight = 100 ' Me.TextBox31.Value
Lnth = 100 ' Me.TextBox32.Value
Width = 20 ' Me.TextBox33.Value
Height = 20 ' Me.TextBox34.Value

Girth = Lnth + (2 * (Width + Height))


If (Weight < 50) And (Girth <= 165) Then
TypeA = True
Else
TypeA = False
End If

If (50 <= Weight) And (Weight < 100) And (Girth <= 165) Then ' <<<<<<<<<<<<<<<<<<<<<<<<<
TypeB = True
Else
TypeB = False
End If

If (100 <= Weight) And (Girth <= 165) Then
TypeC = True
Else
TypeC = False
End If

If TypeA Then
MsgBox "Type A"
' Dim pdA As Range
' Set pdA = ActiveDocument.Bookmarks("t1").Range
' pdA.Text = "A"


ElseIf TypeB Then
MsgBox "Type B"
' Dim pdb As Range
' Set pdb = ActiveDocument.Bookmarks("t1").Range
' pdb.Text = "B"

ElseIf TypeC Then
MsgBox "Type C"
' Dim pdc As Range
' Set pdc = ActiveDocument.Bookmarks("t1").Range
' pdc.Text = "C"
End If
End Sub

[macropod]

It seems to me the code could be reduced to:

Sub parceldelivery1()
Dim weight As Single, girth As Single
weight = Me.TextBox31.Value
With Me
girth = .TextBox32.Value + 2 * (.TextBox33.Value + .TextBox34.Value)
End With
With ActiveDocument.Bookmarks("t1").Range
Select Case girth
Case Is <= 165
Select Case weight
Case Is < 50: .Text = "A"
Case Is >= 100: .Text = "C"
Case Else: .Text = "B"
End Select
End Select
End With
End Sub

[gmaxey]

I have to agree with the Paul(s) here. Your code is overly complicated and you haven't defined what to do if the girth is greater than 165

'l am gonna use the numbers such that i get type C.
'So lets say weight is 100 lbs , length is 100 , bread is 20 and height is 20 .
'So girth is 180. This should qualify for C type .
'However, on running the code , I get B type. What am i doing wrong?



Sub parceldelivery()
Dim oRng As Range
Dim sngGirth As Single
sngGirth = CSng(txtLength) + (2 * CSng(txtWidth) + CSng(txtHeight))
Set oRng = ActiveDocument.Bookmarks("t1").Range
Select Case sngGirth
Case Is <= 165
Select Case CSng(txtWeight)
Case Is < 50: oRng.Text = "A"
Case Is >= 100: oRng.Text = "C"
Case Else: oRng.Text = "B"
End Select
Case Else
'For case when girth is > 165
oRng.Text = "C"
End Select
ActiveDocument.Bookmarks.Add "t1", oRng
End Sub


Also using TextBox31-34 doesn't make it any clearer. They are easy to rename using meaningful terms.

[Paul_Hossler]

thank you for your help! however, something is wrong with the code i am using below to decide if type a ,type b or type c

type c = 100 lbs or greater and girth equal to or less than 165

l am gonna use the numbers such that i get type C. So lets say weight is 100 lbs , length is 100 , bread is 20 and height is 20 . So girth is 180. This should qualify for C type . However, on running the code , I get B type. What am i doing wrong?
thanks!



Certainly NOT a type C

What are the rules since they seem to have changed from your first post