Consulting

Results 1 to 8 of 8

Thread: if ...then ... else command help vba

  1. #1

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

    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

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,838
    Location
    Start by replacing your "&" with And in the condition statements.
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,861
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    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

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,861
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,585
    Location
    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
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,838
    Location
    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.
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,861
    Location
    Quote Originally Posted by sarveshspace View Post
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •