VBA errors Next Block IF

I thought that I have all the blocks correct but I keep getting errors.
If I remove the Next (I think I need this with the for) it gives me Block If error.
If i have it in, it gives me Next with no FOR error, even though I have a for.

Sub Tags()
I uploaded the project
Can anyone help take a second look???
What-if-scenario-v4.xlsm
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.

Martin LissOlder than dirtCommented:
I have no idea what you actually want to have happen but proper indentation would help. For example is this section unrelated to anything else? If so then instead of

For c = LR To 2 Step -1

'If Dom Ex
    If Cells(c, "F") = "FO" Or Cells(c, "F") = "PO" Or Cells(c, "F") = "SO" _
                Or Cells(c, "F") = "EA" Or Cells(c, "F") = "E2" Or Cells(c, "F") = "ES" Then
        If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = Cells(c, "F") & "L"
            If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = Cells(c, "F") & "P"
                If Cells(c, "BB") = "Box" Then Cells(c, "BS") = Cells(c, "F")
End If

Open in new window


you would want
For c = LR To 2 Step -1

'If Dom Ex
    If Cells(c, "F") = "FO" Or Cells(c, "F") = "PO" Or Cells(c, "F") = "SO" _
                Or Cells(c, "F") = "EA" Or Cells(c, "F") = "E2" Or Cells(c, "F") = "ES" Then
        If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = Cells(c, "F") & "L"
            If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = Cells(c, "F") & "P"
                If Cells(c, "BB") = "Box" Then Cells(c, "BS") = Cells(c, "F") THEN
                    ' do something
                End If
             End If
    End If
End If

Open in new window

0
Martin LissOlder than dirtCommented:
If you would describe in words what you want I could probably fix it for you.
0
Bill PrewCommented:
Building on what Martin said, it's clear you don't have nearly enough END IF statements.  Running your code through a VBS formatting tool yielded the following, showing that you have not closed a number of blocks properly - hence the indendation at the end never unwinds back to column 1.

Sub Tags()
   Application.ScreenUpdating = False
   Application.Calculation    = xlCalculationManual
   Dim c As Long
   Dim LR As Long
   LR = Range("E2").End(xlDown).Row

   For c = LR To 2 Step - 1

      'If Dom Ex

      If Cells(c, "F") = "FO" Or Cells(c, "F") = "PO" Or Cells(c, "F") = "SO" _
         Or Cells(c, "F") = "EA" Or Cells(c, "F") = "E2" Or Cells(c, "F") = "ES" Then
         If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = Cells(c, "F") & "L"
         If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = Cells(c, "F") & "P"
         If Cells(c, "BB") = "Box" Then Cells(c, "BS") = Cells(c, "F")
      End If

      ' Dom FR
      If Cells(c, "F") = "F1" Or Cells(c, "F") = "F2" Or Cells(c, "F") = "F3" Then Cells(c, "BS") = "Dom" & Cells(c, "F")

      'PR

      If Cells(c, "F") = "IP" Then

         If Cells(c, "AV") = "Export" And Cells(c, "AT") = "PR" Then
            If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = "IPPREXL"
            If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = "IPPREXP"
            If Cells(c, "BB") = "Box" Then Cells(c, "BS") = "IPPREX"
         End If

         If Cells(c, "F") = "IP" Then

            If Cells(c, "AV") = "Import" And Cells(c, "AT") = "PR" Then
               If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = "IPPRIMPL"
               If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = "IPPRIMPP"
               If Cells(c, "BB") = "Box" Then Cells(c, "BS") = "IPPRIMP"
            End If

            If Cells(c, "F") = "IE" Then

               If Cells(c, "AV") = "Export" And Cells(c, "AT") = "PR" Then
                  Cells(c, "BS") = "IEPREX"
               End If

               If Cells(c, "F") = "IE" Then

                  If Cells(c, "AV") = "Import" And Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPPRIMP"
                  End If

                  If Cells(c, "F") = "IPF" Then

                     If Cells(c, "AV") = "Export" And Cells(c, "AT") = "PR" Then
                        Cells(c, "BS") = "IPFPREX"
                     End If

                     If Cells(c, "F") = "IPF" Then

                        If Cells(c, "AV") = "Import" And Cells(c, "AT") = "PR" Then
                           Cells(c, "BS") = "IPFPRIMP"
                        End If

                        If Cells(c, "F") = "IEF" Then

                           If Cells(c, "AV") = "Export" And Cells(c, "AT") = "PR" Then
                              Cells(c, "BS") = "IEFPREX"
                           End If

                           If Cells(c, "F") = "IEF" Then

                              If Cells(c, "AV") = "Import" And Cells(c, "AT") = "PR" Then
                                 Cells(c, "BS") = "IEFPRIMP"
                              End If

                              'Intl

                              If Cells(c, "F") = "IP" Then

                                 If Cells(c, "AV") = "Export" And Cells(c, "AT") <> "PR" Then
                                    If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = "IPEXL"
                                    If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = "IPEXP"
                                    If Cells(c, "BB") = "Box" Then Cells(c, "BS") = "IPEX"
                                 End If

                                 If Cells(c, "F") = "IP" Then

                                    If Cells(c, "AV") = "Import" And Cells(c, "AT") <> "PR" Then
                                       If Cells(c, "BB") = "Letter" Then Cells(c, "BS") = "IPIMPL"
                                       If Cells(c, "BB") = "Pak" Then Cells(c, "BS") = "IPIMPP"
                                       If Cells(c, "BB") = "Box" Then Cells(c, "BS") = "IPIMP"
                                    End If

                                    If Cells(c, "F") = "IE" Then
                                       If Cells(c, "AV") = "Export" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IEEX"
                                    End If

                                    If Cells(c, "F") = "IE" Then
                                       If Cells(c, "AV") = "Import" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IPIMP"
                                    End If

                                    If Cells(c, "F") = "IPF" Then
                                       If Cells(c, "AV") = "Export" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IPFEX"
                                    End If

                                    If Cells(c, "F") = "IPF" Then
                                       If Cells(c, "AV") = "Import" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IPFIMP"
                                    End If

                                    If Cells(c, "F") = "IEF" Then
                                       If Cells(c, "AV") = "Export" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IEFEX"
                                    End If

                                    If Cells(c, "F") = "IEF" Then
                                       If Cells(c, "AV") = "Import" And Cells(c, "AT") <> "PR" Then Cells(c, "BS") = "IEFIMP"
                                    End If

                                    If Cells(c, "F") = "GR" Then Cells(c, "BS") = "GR"

                                    If Cells(c, "F") = "HD" Then Cells(c, "BS") = "HD"

                                    If Cells(c, "F") = "PRP" Then Cells(c, "BS") = "PRP"

                                 Next

                                 Application.Calculation = xlCalc
                              End Sub

Open in new window

~bp
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Euro5Author Commented:
Ok, I think I get it. I will work on that...
0
Euro5Author Commented:
I am trying to create a tag associated with the named range.

If the column F is FO, PO, SO, E2, EA, ES then
if BB is "Letter" create code in column BS = column F & "L"  (POL, SOL, etc)
if BB is "Pak" create code in column BS = column F & "P"  (POP, SOP, etc)

it is basically that over and over to complete all the named ranges.

I still get an error about block if, but I think I did add them all and tried to align them.
Honestly, I am trying...
What-if-scenario-v4.xlsm
0
Bill PrewCommented:
Yup, I can see you are working it, good.

I think the IF statements are giving you trouble.

There are several forms of the IF statement (http://www.w3schools.com/asp/vbscript_conditionals.asp), but keep in mind that a single statement IF can be written two common ways:

IF A = B Then I = 10

or

IF A = B Then
    I = 10
END IF


For this reason I often only use the last form, it is much easier to align IF's, and spot missing END IF's, etc.

~bp
0
Bill PrewCommented:
Looking at your original code I think could be a lot simpler with usage of SELECT CASE statements, would you be open to that if I took a stab at it?

~bp
0
Martin LissOlder than dirtCommented:
Do it this way. Note line 28 where I got lazy. Add similar cases as I did for the rest of your conditions.

For c = LR To 2 Step -1

'If Dom Ex
    Select Case Cells(c, "F")
        Case "FO", "PO", "SO", "EA", "E2", "ES"
            If Cells(c, "BB") = "Letter" Then
                Cells(c, "BS") = Cells(c, "F") & "L"
            ElseIf Cells(c, "BB") = "Pak" Then
                Cells(c, "BS") = Cells(c, "F") & "P"
            ElseIf Cells(c, "BB") = "Box" Then
                Cells(c, "BS") = Cells(c, "F")
            End If
        
 ' Dom FR
        Case "F1", "F2", "F3"
            Cells(c, "BS") = "Dom" & Cells(c, "F")
     
 'PR
        Case "IP"
            If Cells(c, "AV") = "Export" And Cells(c, "AT") = "PR" Then
                If Cells(c, "BB") = "Letter" Then
                    Cells(c, "BS") = "IPPREXL"
                ElseIf Cells(c, "BB") = "Pak" Then
                    Cells(c, "BS") = "IPPREXP"
                ElseIf Cells(c, "BB") = "Box" Then
                    Cells(c, "BS") = "IPPREX"
            End If
        ' more cases
    End Select
Next

Open in new window

0
Bill PrewCommented:
Here is what I was thinking with SELECT CASE, see what you think:

Sub Tags()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim c As Long, LR As Long
   LR = Range("E2").End(xlDown).Row

   For c = LR To 2 Step -1

      Select Case Cells(c, "F")

         Case "FO", "PO", "SO", "EA", "E2", "ES"
            Select Case Cells(c, "BB")
               Case "Letter"
                  Cells(c, "BS") = Cells(c, "F") & "L"
               Case "Pak"
                  Cells(c, "F") & "P"
               Case "Box"
                  Cells(c, "BS") = Cells(c, "F")
            End Select

         Case "F1", "F2", "F3"
            Cells(c, "BS") = "Dom" & Cells(c, "F")

         Case "IP"
            Select Case Cells(c, "AV")
               Case "Export"
                  If Cells(c, "BS") = "PR" Then
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPPREXL"
                        Case "Pak"
                           Cells(c, "BS") = "IPPREXP"
                        Case "Box"
                           Cells(c, "BS") = "IPPREX"
                     End Select
                  Else
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPEXL"
                        Case "Pak"
                           Cells(c, "BS") = "IPEXP"
                        Case "Box"
                           Cells(c, "BS") = "IPEX"
                     End Select
                  End If
               Case "Import"
                  If Cells(c, "BS") = "PR" Then
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPPRIMPL"
                        Case "Pak"
                           Cells(c, "BS") = "IPPRIMPP"
                        Case "Box"
                           Cells(c, "BS") = "IPPRIMP"
                     End Select
                  Else
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPIMPL"
                        Case "Pak"
                           Cells(c, "BS") = "IPIMPP"
                        Case "Box"
                           Cells(c, "BS") = "IPIMP"
                     End Select
                  End If
            End Select

         Case "IE"
            Select Case Cells(c, "AV")
               Case "Export"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEPREX"
                  Else
                     Cells(c, "BS") = "IEEX"
                  End If
               Case "Import"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPPRIMP"
                  Else
                     Cells(c, "BS") = "IPIMP"
                  End If
            End Select

         Case "IPF"
            Select Case Cells(c, "AV")
               Case "Export"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPFPREX"
                  Else
                     Cells(c, "BS") = "IPFEX"
                  End If
               Case "Import"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPFPRIMP"
                  Else
                     Cells(c, "BS") = "IPFIMP"
                  End If
            End Select

         Case "IEF"
            Select Case Cells(c, "AV")
               Case "Export"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEFPREX"
                  Else
                     Cells(c, "BS") = "IEFEX"
                  End If
               Case "Import"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEFPRIMP"
                  Else
                     Cells(c, "BS") = "IEFIMP"
                  End If
            End Select

         Case "GR"
            Cells(c, "BS") = "GR"

         Case "HD"
            Cells(c, "BS") = "HD"
        
         Case "PRP"
            Cells(c, "BS") = "PRP"

      End Select
        
   Next

   Application.Calculation = xlCalc

End Sub

Open in new window

~bp
0
Euro5Author Commented:
@Bill - this works great - and quickly - but it doesn't complete for IP or IE. Thanks for helping me here!!
0
Bill PrewCommented:
This was do to the fact that VBA is a case sensitive language, and we were matching on "Import" and "Export" when the spreadsheet values were actually "IMPORT" and "EXPORT".  As long as that will always be the case, then the change below should handle that.

Sub Tags()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim c As Long, LR As Long
   LR = Range("E2").End(xlDown).Row

   For c = LR To 2 Step -1

      Select Case Cells(c, "F")

         Case "FO", "PO", "SO", "EA", "E2", "ES"
            Select Case Cells(c, "BB")
               Case "Letter"
                  Cells(c, "BS") = Cells(c, "F") & "L"
               Case "Pak"
                  Cells(c, "BS") = Cells(c, "F") & "P"
               Case "Box"
                  Cells(c, "BS") = Cells(c, "F")
            End Select

         Case "F1", "F2", "F3"
            Cells(c, "BS") = "Dom" & Cells(c, "F")

         Case "IP"
            Select Case Cells(c, "AV")
               Case "EXPORT"
                  If Cells(c, "BS") = "PR" Then
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPPREXL"
                        Case "Pak"
                           Cells(c, "BS") = "IPPREXP"
                        Case "Box"
                           Cells(c, "BS") = "IPPREX"
                     End Select
                  Else
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPEXL"
                        Case "Pak"
                           Cells(c, "BS") = "IPEXP"
                        Case "Box"
                           Cells(c, "BS") = "IPEX"
                     End Select
                  End If
               Case "IMPORT"
                  If Cells(c, "BS") = "PR" Then
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPPRIMPL"
                        Case "Pak"
                           Cells(c, "BS") = "IPPRIMPP"
                        Case "Box"
                           Cells(c, "BS") = "IPPRIMP"
                     End Select
                  Else
                     Select Case Cells(c, "BB")
                        Case "Letter"
                           Cells(c, "BS") = "IPIMPL"
                        Case "Pak"
                           Cells(c, "BS") = "IPIMPP"
                        Case "Box"
                           Cells(c, "BS") = "IPIMP"
                     End Select
                  End If
            End Select

         Case "IE"
            Select Case Cells(c, "AV")
               Case "EXPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEPREX"
                  Else
                     Cells(c, "BS") = "IEEX"
                  End If
               Case "IMPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPPRIMP"
                  Else
                     Cells(c, "BS") = "IPIMP"
                  End If
            End Select

         Case "IPF"
            Select Case Cells(c, "AV")
               Case "EXPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPFPREX"
                  Else
                     Cells(c, "BS") = "IPFEX"
                  End If
               Case "IMPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IPFPRIMP"
                  Else
                     Cells(c, "BS") = "IPFIMP"
                  End If
            End Select

         Case "IEF"
            Select Case Cells(c, "AV")
               Case "EXPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEFPREX"
                  Else
                     Cells(c, "BS") = "IEFEX"
                  End If
               Case "IMPORT"
                  If Cells(c, "AT") = "PR" Then
                     Cells(c, "BS") = "IEFPRIMP"
                  Else
                     Cells(c, "BS") = "IEFIMP"
                  End If
            End Select

         Case "GR"
            Cells(c, "BS") = "GR"

         Case "HD"
            Cells(c, "BS") = "HD"
        
         Case "PRP"
            Cells(c, "BS") = "PRP"

      End Select
        
   Next

   Application.Calculation = xlCalc

End Sub

Open in new window

~bp
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
Euro5Author Commented:
Wow, this was great! I learned so much with this question. Thanks for helping.
0
Bill PrewCommented:
@Euro5,

Thanks for the feedback, enjoyed lending a hand and very glad that you picked up some ideas for future use, that's even more important than just getting a problem resolved so glad my ideas were useful.

~bp
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.