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

Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
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

0
Euro5Author Commented:
I have an error on line 67
Case w/o select case
0
Shaun KlineLead Software EngineerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Euro5Author Commented:
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

0
Shaun KlineLead Software EngineerCommented:
On line 91 of your revised code, you have an End Select out of place.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosCommented:
Also @ Line 164... screen updating TRUE / FALSE  :-)
1
Euro5Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.