Blatt Kristóf
Reputation: 21
Standard, but specific "End With without With" Compile error
Given an Standard compile error "End WIth without With" msgbox. I have just started vba coding. Pls give some instruction to let me step forward. (As the code has been inserted, tabulators got wrong. Do not bother with that.)
I have checked IF statements, object correctness, included loops.
'*2/B. - Generate template based new excel file and fill up cells with data of document attributes*
For n = 1 To documentCount
Dim strTemplate As String: strTemplate = "C:\Users\C3642\Desktop\FU5504-Elfogadhatosagi_Nyilatkozat_formanyomtatvany\PA2-FU-5504-NY-01_v2.xlsx"
Set outXl = xlApp.Workbooks.Open(strTemplate, True)
Set outXl = ActiveWorkbook
With outXl.Worksheets(1)
.SaveAs Filename:="C:\Users\C3642\Desktop\FU5504-Elfogadhatosagi_Nyilatkozat_formanyomtatvany\" & docCode(n) & "_" & docResponsible(n) & ".xlsx", FileFormat:=xlOpenXMLWorkbookMacroEnabled
.Cells(5, 4).Value = docName(n)
.Cells(6, 4).Value = docCode(n)
'.Cells(6, 13).Value = docRevision(n) <- nincs hozzá adat :(
.Cells(14, 4).Value = docResponsible(n)
If departmentHead(n) = "Name1" Then
.Cells(14, 7).Value = "VIO"
.Cells(31, 7).Value = "VIO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name2" Then
.Cells(14, 7).Value = "ITO"
.Cells(31, 7).Value = "ITO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name3" Then
.Cells(14, 7).Value = "VSKO"
.Cells(31, 7).Value = "VSKO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name4" Then
.Cells(14, 7).Value = "AMO"
.Cells(31, 7).Value = "AMO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name5" Then
.Cells(14, 7).Value = "NUO"
.Cells(31, 7).Value = "NUO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name6" Then
.Cells(14, 7).Value = "KGO"
.Cells(31, 7).Value = "KGO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name7" Then
.Cells(14, 7).Value = "PGO"
.Cells(31, 7).Value = "PGO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name8" Then
.Cells(14, 7).Value = "ETO"
.Cells(31, 7).Value = "ETO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name9" Then
.Cells(14, 7).Value = "GMDO"
.Cells(31, 7).Value = "GMDO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name10" Then
.Cells(14, 7).Value = "ABO"
.Cells(31, 7).Value = "ABO"
.Cells(31, 4).Value = departmentHead(n)
Else
.Cells(14, 7).Value = ""
.Cells(31, 7).Value = ""
.Cells(31, 4).Value = ""
'.Cells(31, 4).Value = supervisorName(n)
'.Cells(31, 7).Value = supervisorDepartment(n)
.Cells(33, 4).Value = "Name0"
Dim innerReviewers() As String: Dim split_ETO_reviewers() As String: Dim split_KGO_reviewers() As String: Dim split_PGO_reviewers() As String: Dim split_NUO_reviewers() As String
Dim split_AMO_reviewers() As String: Dim split_VSKO_reviewers() As String: Dim split_VIO_reviewers() As String: Dim split_ABO_reviewers() As String: Dim split_ITO_reviewers() As String
Dim split_UIG_reviewers() As String: Dim split_ENBO_reviewers() As String: Dim split_LETO_reviewers() As String: Dim split_Non_ERBE_reviewers() As String: Dim split_ERBE_reviewers() As String
innerReviewers() = Split(reviewers(n), ",")
split_ETO_reviewers() = Split(ETO_reviewers(n), ",")
split_KGO_reviewers() = Split(KGO_reviewers(n), ",")
split_PGO_reviewers() = Split(PGO_reviewers(n), ",")
split_NUO_reviewers() = Split(NUO_reviewers(n), ",")
split_AMO_reviewers() = Split(AMO_reviewers(n), ",")
split_VSKO_reviewers() = Split(VSKO_reviewers(n), ",")
split_VIO_reviewers() = Split(VIO_reviewers(n), ",")
split_ABO_reviewers() = Split(ABO_reviewers(n), ",")
split_ITO_reviewers() = Split(ITO_reviewers(n), ",")
split_UIG_reviewers() = Split(UIG_reviewers(n), ",")
split_ENBO_reviewers() = Split(ENBO_reviewers(n), ",")
split_LETO_reviewers() = Split(LETO_reviewers(n), ",")
split_Non_ERBE_reviewers() = Split(Non_ERBE_reviewers(n), ",")
split_ERBE_reviewers() = Split(ERBE_reviewers(n), ",")
reviewersCount = 0
If IsEmpty(innerReviewers) Or (IsEmpty(innerReviewers) & IsEmpty(split_ETO_reviewers) & IsEmpty(split_KGO_reviewers) & IsEmpty(split_PGO_reviewers) & IsEmpty(split_NUO_reviewers) & IsEmpty(split_AMO_reviewers) & IsEmpty(split_VSKO_reviewers) & IsEmpty(split_VIO_reviewers) & IsEmpty(split_ABO_reviewers) & IsEmpty(split_ITO_reviewers)) Then
MsgBox "The current document has not been reviewed by colleques! (Archiving function of list of non-reviewed docs is not ready.)", vbOKOnly, "Warning for lacking reviewers!"
Else
reviewersCount = UBound(innerReviewers) - LBound(innerReviewers) + 1
Dim i As Integer
i = 0
Do While (i < reviewersCount & i < 12)
.Cells(15 + i, 4).Value = innerReviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_ETO_reviewers) - LBound(split_ETO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = ETO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_KGO_reviewers) - LBound(split_KGO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = KGO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_PGO_reviewers) - LBound(split_PGO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = PGO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_NUO_reviewers) - LBound(split_NUO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = NUO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_AMO_reviewers) - LBound(split_AMO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = AMO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_VSKO_reviewers) - LBound(split_VSKO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = VSKO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_VIO_reviewers) - LBound(split_VIO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = VIO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_ABO_reviewers) - LBound(split_ABO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = ABO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_ITO_reviewers) - LBound(split_ITO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = ITO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_UIG_reviewers) - LBound(split_UIG_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = UIG_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_ENBO_reviewers) - LBound(split_ENBO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = ENBO_reviewers(i)
i = i + 1
Loop
reviewersCount = reviewersCount + UBound(split_LETO_reviewers) - LBound(split_LETO_reviewers) + 1
Do While (i < (12 - reviewersCount))
.Cells(15 + i, 4).Value = LETO_reviewers(i)
i = i + 1
Loop
'reviewersCount = reviewersCount + UBound(split_Non_ERBE_reviewers) - LBound(split_Non_ERBE_reviewers) + 1
'Do While (i < (12 - reviewersCount))
' .Cells(15 + i, 4).Value = Non_ERBE_reviewers(i)
' i = i + 1
'Loop
'reviewersCount = reviewersCount + UBound(split_ERBE_reviewers) - LBound(split_ERBE_reviewers) + 1
'Do While (i < (12 - reviewersCount))
' .Cells(15 + i, 4).Value = ERBE_reviewers(i)
' i = i + 1
'Loop
End With
outXl.Close SaveChanges:=True
Next n
Maybe Workbook object are not setted properly (but it is declared in my code).
Upvotes: 2
Views: 85
Answers (3)
Mathieu Guindon
Reputation: 71167
As the code has been inserted, tabulators got wrong. Do not bother with that.
And yet, running your code through an indenter (disclaimer: that's the Rubberduck Smart Indenter port; Rubberduck being a free & open-source VBIDE add-in project I manage) gives a major clue about what's going on:
If departmentHead(n) = "Name1" Then
.Cells(14, 7).Value = "VIO"
.Cells(31, 7).Value = "VIO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name2" Then
.Cells(14, 7).Value = "ITO"
.Cells(31, 7).Value = "ITO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name3" Then
.Cells(14, 7).Value = "VSKO"
.Cells(31, 7).Value = "VSKO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name4" Then
.Cells(14, 7).Value = "AMO"
.Cells(31, 7).Value = "AMO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name5" Then
.Cells(14, 7).Value = "NUO"
.Cells(31, 7).Value = "NUO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name6" Then
.Cells(14, 7).Value = "KGO"
.Cells(31, 7).Value = "KGO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name7" Then
.Cells(14, 7).Value = "PGO"
.Cells(31, 7).Value = "PGO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name8" Then
.Cells(14, 7).Value = "ETO"
.Cells(31, 7).Value = "ETO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name9" Then
.Cells(14, 7).Value = "GMDO"
.Cells(31, 7).Value = "GMDO"
.Cells(31, 4).Value = departmentHead(n)
If departmentHead(n) = "Name10" Then
.Cells(14, 7).Value = "ABO"
.Cells(31, 7).Value = "ABO"
.Cells(31, 4).Value = departmentHead(n)
Else
.Cells(14, 7).Value = ""
.Cells(31, 7).Value = ""
.Cells(31, 4).Value = ""
All these If blocks are actually nested, because they're never terminated with an End If token. So when the compiler reaches an End With token, it doesn't know what to line it up against, because it's still expecting to close the inner-most If block.
Rule of thumb, when you have repeated conditionals all evaluating the same value, you want a Select...Case block - also consider reducing the redundancies by extracting the changing string value into its own local variable:
Dim result As String
Select Case departmentHead(n)
Case "Name1"
result = "VIO"
Case "Name2"
result = "ITO"
Case "Name3"
result = "VSKO"
'...
End Select
.Cells(14, 7).Value = result
.Cells(31, 7).Value = result
If result <> vbNullString Then .Cells(31, 4) = departmentHead(n)
Upvotes: 3
Luke G.
Reputation: 587
It's expecting a bunch of End If or ElseIfs, and not seeing them it's complaining about the first unclosed statement (the with).
For example, fix it to:
...
If departmentHead(n) = "Name1" Then
.Cells(14, 7).Value = "VIO"
.Cells(31, 7).Value = "VIO"
.Cells(31, 4).Value = departmentHead(n)
ElseIf departmentHead(n) = "Name2" Then
.Cells(14, 7).Value = "ITO"
.Cells(31, 7).Value = "ITO"
.Cells(31, 4).Value = departmentHead(n)
ElseIf departmentHead(n) = "Name3" Then
.Cells(14, 7).Value = "VSKO"
.Cells(31, 7).Value = "VSKO"
.Cells(31, 4).Value = departmentHead(n)
...
Else
.Cells(14, 7).Value = ""
.Cells(31, 7).Value = ""
.Cells(31, 4).Value = ""
End If
Upvotes: 3
Vityata
Reputation: 43575
The error shows up, because there is a End If missing everywhere.
E.g.:
If departmentHead(n) = "Name3" Then
.Cells(14, 7).Value = "VSKO"
.Cells(31, 7).Value = "VSKO"
.Cells(31, 4).Value = departmentHead(n)
End If
Upvotes: 4
Related Questions
- "Compile Error: End With without With" although there is a With block start
- End With without With and End If without If compile error
- VBA expected end of statement
- Compile error: End with without with. BUT THE WITH IS THERE
- Compile Error, Next without for
- End With without With
- Compile error "expected: end of statement"
- Compile error End with without with
- New to VB. My code generates the error: "expecting End With". How do the with-end with statements work?
- Excel VBA error End with with out with?