Consulting

Results 1 to 14 of 14

Thread: Quick VBA Question - Adding Another Condition - Use ElseIf?

  1. #1

    Quick VBA Question - Adding Another Condition - Use ElseIf?

    I am trying to add a line of code in VBA that will ask if the range has a value of HP-1 to then to make it 16 GA. else, it needs to be 26 GA. Currently code just converts everything to 26 GA.

    If Sheets "TRIM".Visible Then
         Sheets("TRIM").Select
         Range("D13").Select
         Do Until ActiveCell.Offset(0, -1).Value =""
               If ActiveCell.Value <>""Then
                    ActiveCell.Value ="26 GA."
                End If
                ActiveCell.Offset(1, 0).Select
            Loop End If

    So I need to add in something that will do the HP-1 condition...I did try to make an Else statement but getting NO compile errors but getting this one.

    compile_error_01.jpg

    (this error has nothing to do with 32bit/64 bit as we are all still using 32bit Excel with 32bit VBA code)
    I'm sure it is pretty easy but can't seem to get the syntax down...
    Any suggestions?

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    327
    Location
    Why is there no space in front of Then?

    Test for the HP-1 value first then if field is empty.

    If field is empty you want it left empty?
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Quote Originally Posted by June7 View Post
    Why is there no space in front of Then?

    Test for the HP-1 value first then if field is empty.

    If field is empty you want it left empty?
    If Sheets("TRIM").Visible Then        
       Sheets("TRIM").Select
       Range("D13").Select
       Do Until ActiveCell.Offset(0, -1).Value = ""
       If ActiveCell.Value = "HP-1" Then
          ActiveCell.Value = "16 GA."
          Else
            If ActiveCell.Value <> "" Then
                ActiveCell.Value = "26 GA."
             End If
    '         ActiveCell.Offset(1, 0).Select
    '        Loop
    '    End If
    This is what I created and is throwing back that weird compile error.

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    327
    Location
    You have 3 multi-line If Then but only 2 End If

    Options:

    1. add End If before ActiveCell.Offset

    2. make nested If a one-liner: If ActiveCell.Value <> "" Then ActiveCell.Value = "26 GA."

    3. use ElseIf
    Last edited by June7; 02-02-2023 at 05:26 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Are you asking if cell D13 has a value "HP-1" then change it to "G26"?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Quote Originally Posted by Aussiebear View Post
    Are you asking if cell D13 has a value "HP-1" then change it to "G26"?
    If it has HP-1, it needs to be 16 GA. else it needs to be 26 GA.

    The HP-1 is something new as we just had verything go to 26 GA.

    This IF/THEN/LOOP takes place in a function...here is the full function

    function_01.jpg

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Sorry but I'm not following the logic of your code here.
    If Sheets("Trim".Visible Then
       With Range("D13")
        If .Value = "HP-1" then
           .Value = "16 GA"
        Else
            .Value = "26 GA"
       End If
    End If
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    Quote Originally Posted by Aussiebear View Post
    Sorry but I'm not following the logic of your code here.
    If Sheets("Trim".Visible Then
       With Range("D13")
        If .Value = "HP-1" then
           .Value = "16 GA"
        Else
             .Value = "26 GA"
       End If
    End If
    The code in this project has been cobbled together for years and I just maintain it...and it has worked this way for years but let me try it this way...

  9. #9
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    That's fine, but can you tell me why this line is in your code?
    Do Until ActiveCell.Offset(0, -1).Value = ""
    
    If all we are checking is the Range D13 and changing it value depending if its blank or "HP-1", how do we effect cell C13?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I tried to copy the code to play with it, but you pasted it as a picture and I really don't want to type all that in

    Use the [#] icon and paste the macro between the [CODE] ....[/CODE ] tags for readability

    Option Explicit
    
    
    Sub Hello()
        MsgBox "Hello World!!!'"
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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

  11. #11
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Here you go Paul
    Private Function AdjustmentsForSpecCond ()
    If Sheets ("Purlin Girt").visible Then
       Punches
       Slopes
       Material
    End If
    If Sheets ("Members).Visible Then
       Sheets("Members").Select
       Range ("D13").Select
       Do Until ActiveCell.Offset(0,-1).Value = " "
          Select Case True
             Case UCase (ActiveCell.Value)= "N.A."
                Activecell.Value = "Built to FL"
             Case UCase (ActiveCell.Value =. "N A"
                Activecell.Value = "Built to FL"
             Case UCase (ActiveCell.Value) = "N/A"
                Activecell.Value = "Built to FL"
             Case UCase (ActiveCell.Value) = "NA"
                Activecell.Value = "Built to FL"
             Case (ActiveCell.Offset(1,0).Value <> " "
                Range ("D3").End(xlDown).Select
             Case Else
                ActiveCell.OffSet(1,0).Select
            End Select
       Loop
    End If
    If Sheets ("Trim").Visible Then
       Sheets("Trim").Select
       Range ("D13").Select
       Do Until ActiveCell.Offset(0,-1).Value = " "
          If ActiveCell.Value <> " " Then
             ActiveCell.Value = "26 GA."
         End If
         ActiveCell.Offset (1,0).Select
      Loop
    End If
    End Function
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    @AussieBear - thanks

    @garak0410 - say thanks to AussieBear

    "The code in this project has been cobbled together for years"


    1. This seems more like a Sub than a Function, although either will work

    2. Suggestions below are not tested, but there was a lot of redundant code and the logic was hard to follow

    3. Not necessary to .Select things like worksheets or cells to work on them

    Option Explicit
    
    
    Private Sub AdjustmentsForSpecCond()
        Dim rCell As Range
    
    '    If Sheets("Purlin Girt").Visible Then  ' <<<<<<<<I assume these are subs
    '       Punches
    '       Slopes
    '       Material
    '    End If
       
        With Worksheets("Members")
        
            Set rCell = .Range("D13")
          
            Do While Len(rCell.Value) > 0
                Select Case UCase(rCell.Value)
                    Case "N.A.", "N A", "N/A", "NA"
                        rCell.Value = "Built to FL"
              End Select
           
              Set rCell = rCell.Offset(1, 0)
           
           Loop
        End If
    
    
    '    If Sheets("Trim").Visible Then
    '       Sheets("Trim").Select
    '       Range("D13").Select
    '       Do Until ActiveCell.Offset(0, -1).Value = " "
    '          If ActiveCell.Value <> " " Then
    '             ActiveCell.Value = "26 GA."
    '         End If
    '         ActiveCell.Offset(1, 0).Select
    '      Loop
    '    End If
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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

  13. #13
    In my opinion, there should be a space in front of Then.
    have HP-1, then it should be 16 GA. Otherwise it needs to be 26 GA.
    slope ball

  14. #14
    Banned VBAX Newbie
    Joined
    Nov 2023
    Posts
    2
    Location
    I think that Then should have a space in front of it.
    possess HP-1, then 16 GA should be used. If not, it must be 26 GA.
    run 3

Tags for this Thread

Posting Permissions

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