Reputation: 93
The following is a macro I am creating, and I've come across an issue when running the code. For some reason when I run it a compile error occurs saying "Next without For", as it identify the "Next l" at the end of the code, but it doesn't identify the "For l = 7 To LastRow" in the first line. Does anyone have any idea why this compile error is occuring?
For l = 7 To LastRow
For i = 3 To LastColumn
If sht.Cells(l, i).Value = "" Then
V = sht.Cells(6, i).Value
sTemp = sTemp & "," & V
End If
Next i
sTemp = Mid(sTemp, 2)
If Not Len(Trim(sTemp)) = 0 Then
BookMarksToDelete = Split(sTemp, ",")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(FlName)
For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
Set pRng = wdDoc.Bookmarks(BookMarksToDelete(i)).Range
pRng.MoveEnd wdParagraph, 2
pRng.Delete
Next i
End If
Set wdTable = wdDoc.Tables(1)
For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
For Each cell In myRangeRef
If InStr(1, cell.Value, BookMarksToDelete(i), vbTextCompare) Then
aCell = cell.Offset(, -1).Value
stTemp = stTemp & "," & aCell
End If
Next cell
Next i
stTemp = Mid(stTemp, 2)
If Not Len(Trim(stTemp)) = 0 Then
ReturnsToDelete = Split(stTemp, ",")
For i = LBound(ReturnsToDelete) To UBound(ReturnsToDelete)
For j = wdTable.Rows.Count To 2 Step -1
If Left(wdTable.cell(j, 1).Range.Text, Len(wdTable.cell(j, 1).Range.Text) - 2) = ReturnsToDelete(i) Then wdTable.Rows(j).Delete
Next j
Next i
End If
With ThisWorkbook.Sheets("Client Database")
firstName = .Range("B" & l)
lastName = .Range("A" & l)
If firstName = Chr(13) & Chr(7) Then
titleName = lastName
Else
titleName = lastName & ", " & firstName
End If
End With
Set tRng = wdDoc.Bookmarks("TitlePageName").Range
tRng.Text = wdDoc.Bookmarks("TitlePageName").Range.Text & titleName
wdDoc.Bookmarks.Add "TitlePageName", tRng
If Date <= 31 / 3 / 2017 Then
quartDate = "March 31" & ", " & Year(Date)
ElseIf 31 / 3 / 2017 <= Date <= 30 / 6 / 2017 Then
quartDate = "June 30" & ", " & Year(Date)
ElseIf 30 / 6 / 2017 <= Date <= 30 / 9 / 2017 Then
quartDate = "September 30" & ", " & Year(Date)
Else
quartDate = "December 31" & ", " & Year(Date)
d = "Information as of" & " " & quartDate
Set dRng = wdDoc.Bookmarks("TitlePageDate").Range
dRng.Text = wdDoc.Bookmarks("TitlePageDate").Range.Text & d
wdDoc.Bookmarks.Add "TitlePageDate", dRng
Set wRng = wdApp.ActiveDocument.Bookmarks("FundCommentary").Range
wRng.Collapse wdCollapseStart
wRng.InsertBreak wdPageBreak
Set sRng = wdApp.ActiveDocument.Bookmarks("Disclaimer").Range
sRng.Collapse wdCollapseStart
sRng.InsertBreak wdPageBreak
Set mRng = wdApp.ActiveDocument.Bookmarks("MonitoringChecklist").Range
mRng.Collapse wdCollapseStart
mRng.InsertBreak wdPageBreak
myArray = Array("CreditMon", "UncorrelatedMon", "FixedMon")
myArray2 = Array("CreditMon2", "UncorrelatedMon2", "FixedMon2")
LastTable = wdDoc.Tables.Count
t = 3
Do Until t = LastTable + 1
Set wdTable = wdDoc.Tables(t)
For i = LBound(ReturnsToDelete) To UBound(ReturnsToDelete)
For j = wdTable.Columns.Count To 2 Step -1
If Left(wdTable.cell(1, j).Range.Text, Len(wdTable.cell(1, j).Range.Text) - 2) = ReturnsToDelete(i) Then wdTable.Columns(j).Delete
Next j
Next i
If wdTable.Columns.Count = 1 Then
wdTable.Delete
t = t
LastTable = wdDoc.Tables.Count
Else
wdTable.Rows.Alignment = wdAlignRowLeft
t = t + 1
End If
Loop
For i = 0 To 2
If wdDoc.Bookmarks.Exists(myArray2(i)) = False Then
wdDoc.Bookmarks(myArray(i)).Range.Delete
Else
wdDoc.Bookmarks(myArray(i)).Range.Collapse wdCollapseStart
wdDoc.Bookmarks(myArray(i)).Range.InsertBreak wdPageBreak
wdDoc.TablesOfContents(1).Update
wdDoc.Repaginate
shName = "PQ Quarterly Reporting for " & firstName & " " & lastName
With wdApp.ActiveDocument
.SaveAs2 "https://path/" & shName & ".docx"
.Close
End With
wdApp.Visible = False
sTemp = ""
stTemp = ""
Next l
Upvotes: 1
Views: 48
Reputation: 8868
You are missing a Next statement on the following piece of code:
For i = 0 To 2
If wdDoc.Bookmarks.Exists(myArray2(i)) = False Then
wdDoc.Bookmarks(myArray(i)).Range.Delete
Else
wdDoc.Bookmarks(myArray(i)).Range.Collapse wdCollapseStart
wdDoc.Bookmarks(myArray(i)).Range.InsertBreak wdPageBreak
Upvotes: 2