Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

vba select case adding condition

I tried to add new conditions to the Select case but can't get it right.

I needed to add to only IP & IE
Within each there are two selects -   = "PR" & <> "PR"
Within those there has to be <=150 & >150
I tried to do it shown below, but I have errors.

Can anyone help?

Sub Tags()


   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   #If VBA7 And Win64 Then
   Dim c As Longlong, lr As Longlong
   Dim ratesh1 As String
   
   #Else
   Dim c As Long, lr As Long
   Dim ratesh1 As String
   #End If
   
   ratesh1 = Sheets("Selection").Range("B11")

   
   Detail.Activate
   lr = Range("A2").End(xlDown).Row

   For c = lr To 2 Step -1

      Select Case Cells(c, "A")

         Case "FO", "PO", "SO", "E2", "E2AM", "ESP"
            Select Case Cells(c, "B")
               Case "Letter"
                  Cells(c, "S") = Cells(c, "A") & "L_" & ratesh1
               Case "Pak"
                  Cells(c, "S") = Cells(c, "A") & "P_" & ratesh1
               Case "Box"
                  Cells(c, "S") = Cells(c, "A") & "_" & ratesh1
            End Select

         Case "F1", "F2", "F3"
            Cells(c, "S") = "Dom_" & Cells(c, "A") & "_" & ratesh1

         Case "IP"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_EX_PR_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_EX_PR_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_EX_PR_" & ratesh1
                     End Select
                  Else
                     If Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                           Cells(c, "S") = "IPHW_EX_PR_" & ratesh1
                  Else
                  If Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_EX_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_EX_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_EX_" & ratesh1
                     End Select
                  Else
                   If Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                        Cells(c, "S") = "IPHW_EX_" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_IMP_PR_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_IMP_PR_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_IMP_PR_" & ratesh1
                     End Select
                  Else
                  If Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_IMP_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_IMP_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_IMP_" & ratesh1
                     End Select
                  Else
                   If Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                        Cells(c, "S") = "IPHW_IMP_" & ratesh1
                     End Select
                  End If
            End Select

         Case "IE"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_EX_PR_" & ratesh1
                  Else
                  If Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_EX_PR" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_IMP_PR_" & ratesh1
                  Else
                    If Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_EX_" & ratesh1
                  Else
                    If Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_IMP_" & ratesh1
                  Else
                    If Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_IMP_" & ratesh1
                  End If
            End Select

         Case "IPF"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IPF_EX_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IPF_EX_" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IPF_IMP_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IPF_IMP_" & ratesh1
                  End If
            End Select

         Case "IEF"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IEF_EX_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IEF_EX_" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IEF_IMP_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IEF_IMP_" & ratesh1
                  End If
            End Select

         Case "GR"
            Cells(c, "S") = "GR_" & ratesh1

         Case "HD"
            Cells(c, "S") = "HD_" & ratesh1
        
         Case "PRP"
            Cells(c, "S") = "PRP_" & ratesh1

      End Select
        
   Next

   Application.ScreenUpdating = False

End Sub

Open in new window

Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image
Instead of using:
Else
If <your statements> Then

Open in new window

use:
ElseIf <your statements> Then

Open in new window


For example, from your code, lines 50-53:
                  Else
                     If Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                           Cells(c, "S") = "IPHW_EX_PR_" & ratesh1
                  Else
                  If Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then

Open in new window


Should look like:
                  ElseIf Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                           Cells(c, "S") = "IPHW_EX_PR_" & ratesh1
                  ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then

Open in new window

Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

I have an error on line 67
Case w/o select case
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image
Yes, that is because all of the IF statements in your code do not have a corresponding End IF. Using the ElseIF will make that error go away.
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

No, I mean I did that and I get the error.
Here is what I have...

Sub Tags()


   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   #If VBA7 And Win64 Then
   Dim c As Longlong, lr As Longlong
   Dim ratesh1 As String
   
   #Else
   Dim c As Long, lr As Long
   Dim ratesh1 As String
   #End If
   
   ratesh1 = Sheets("Selection").Range("B11")

   
   Detail.Activate
   lr = Range("A2").End(xlDown).Row

   For c = lr To 2 Step -1

      Select Case Cells(c, "A")

         Case "FO", "PO", "SO", "E2", "E2AM", "ESP"
            Select Case Cells(c, "B")
               Case "Letter"
                  Cells(c, "S") = Cells(c, "A") & "L_" & ratesh1
               Case "Pak"
                  Cells(c, "S") = Cells(c, "A") & "P_" & ratesh1
               Case "Box"
                  Cells(c, "S") = Cells(c, "A") & "_" & ratesh1
            End Select

         Case "F1", "F2", "F3"
            Cells(c, "S") = "Dom_" & Cells(c, "A") & "_" & ratesh1

         Case "IP"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_EX_PR_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_EX_PR_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_EX_PR_" & ratesh1
                     End Select
                  
                     ElseIf Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                           Cells(c, "S") = "IPHW_EX_PR_" & ratesh1
                 
                  ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_EX_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_EX_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_EX_" & ratesh1
                       End Select
                 
                   ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                        Cells(c, "S") = "IPHW_EX_" & ratesh1
                  End If
                 
              Case "IMPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_IMP_PR_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_IMP_PR_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_IMP_PR_" & ratesh1
                     End Select
                 
                  ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Select Case Cells(c, "B")
                        Case "Letter"
                           Cells(c, "S") = "IPL_IMP_" & ratesh1
                        Case "Pak"
                           Cells(c, "S") = "IPP_IMP_" & ratesh1
                        Case "Box"
                           Cells(c, "S") = "IP_IMP_" & ratesh1
                     End Select
                     
                 ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                        Cells(c, "S") = "IPHW_IMP_" & ratesh1
                     End Select
                  End If
            End Select

         Case "IE"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_EX_PR_" & ratesh1
                  
                  ElseIf Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_EX_PR" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_IMP_PR_" & ratesh1
                  
                    ElseIf Cells(c, "J") = "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_EX_" & ratesh1
                  
                    ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") <= 150 Then
                     Cells(c, "S") = "IE_IMP_" & ratesh1
                
                    ElseIf Cells(c, "J") <> "PR" And Cells(c, "M") > 150 Then
                     Cells(c, "S") = "IEHW_IMP_" & ratesh1
                  End If
            End Select

         Case "IPF"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IPF_EX_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IPF_EX_" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IPF_IMP_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IPF_IMP_" & ratesh1
                  End If
            End Select

         Case "IEF"
            Select Case Cells(c, "K")
               Case "EXPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IEF_EX_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IEF_EX_" & ratesh1
                  End If
               Case "IMPORT"
                  If Cells(c, "J") = "PR" Then
                     Cells(c, "S") = "IEF_IMP_PR_" & ratesh1
                  Else
                     Cells(c, "S") = "IEF_IMP_" & ratesh1
                  End If
            End Select

         Case "GR"
            Cells(c, "S") = "GR_" & ratesh1

         Case "HD"
            Cells(c, "S") = "HD_" & ratesh1
        
         Case "PRP"
            Cells(c, "S") = "PRP_" & ratesh1

      End Select
        
   Next

   Application.ScreenUpdating = False

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DrTribos
DrTribos
Flag of Australia image
Also @ Line 164... screen updating TRUE / FALSE  :-)
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Thanks!