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
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
If you would describe in words what you want I could probably fix it for you.
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 SubASKER
Ok, I think I get it. I will work on that...
ASKER
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
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
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
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
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
~bp
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
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 SubASKER
@Bill - this works great - and quickly - but it doesn't complete for IP or IE. Thanks for helping me here!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, this was great! I learned so much with this question. Thanks for helping.
@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
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
Open in new window
you would want
For c = LR To 2 Step -1
Open in new window