Receiving "Compile Error: Next without For" when attempting to insert new formatting instructions to existing code

Question

I have made an amendment to existing code (my insertion double asterisk below). The existing code works fine without my addition, and I'm not sure why once including the additional cell border formatting I receive the above prompt, "Compile Error: Next without For". The line of code where I am receiving this error message is also double asterisk. I have looked through existing threads and none of the advice seems applicable. Please can someone assist? Thanks!

     `For Each WS In Worksheets
    'This code will delete the debt financing in the Property Summary tab
    With Worksheets("Property Summary").Activate

      'Range might need to be updated if it has moved around due to AE 
        version updates.
        Range("E27:H36").Delete
        Range("E27:H36").Select
        Selection.ClearContents
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    
        **Range("E26:H26").Select
        Selection.Border(xlEdgeLeft).LineStyle = xlNone
        Selection.Border(xlEdgeRight).LineStyle = xlNone
        With Selection.Border(xlEdgeTop)
        .Pattern = xlContinuous
        .TintAndShade = 5
        .Weight = xlThin
        End With**
    

    'This code will clean up the Cash Flow Report tab
    With Worksheets("Cash Flow").Activate
        
        'Range might need to be updated if it has moved around due to AE version updates
        Range("A2").Select
    
        Cells.Replace What:=" (Amounts in EUR)", Replacement:="", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        
        Cells.Replace What:=" (Amounts in PLN)", Replacement:="", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
       
        Cells.Replace What:=" (Amounts in USD)", Replacement:="", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        
        NomProperty = Range("A2").Value

        'Blue
        Range("B8:L8").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With
        
        'Clean Up
                    
        'This will unmerge the first 4 rows (A1:M4) and delete columns M:Z
        Range("A1:M4").Select
        Selection.UnMerge
        Columns("L:Z").Select
        Selection.Delete Shift:=xlToLeft
                  
        'This will delete unwanted rows and minimize rows
        For i = 9 To 100
            
            If Cells(i, 1).Value = "" Or Cells(i, 1).Value = " " Or Cells(i, 1).Value = "  " Or 
            Cells(i, 1).Value = "   " Or Cells(i, 1).Value = "    " Then
                Rows(i).RowHeight = 3
            GoTo Following
            End If
            
            If Cells(i, 1).Value = "  Scheduled Base Rent" Then GoTo Following
            If Cells(i, 1).Value = "  CPI Increases" Then GoTo Following
            If Cells(i, 1).Value = "  Free CPI Increases" Then GoTo Following
            If Cells(i, 1).Value = "Total Rental Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Total Other Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Potential Gross Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Total Vacancy & Credit Loss" Then GoTo Following
            If Cells(i, 1).Value = "Effective Gross Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Net Operating Income" Then GoTo Following
            If Cells(i, 1).Value = "  Total Capital Expenditures" Then GoTo Following
            If Cells(i, 1).Value = "Total Leasing & Capital Costs" Then GoTo Following
            If Cells(i, 1).Value = "Cash Flow Before Debt Service" Then GoTo Following
            If Cells(i, 1).Value = "Cash Flow Available for Distribution" Then GoTo Following
            If Cells(i, 1).Value = "Total Other Tenant Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Total Tenant Revenue" Then GoTo Following
            If Cells(i, 1).Value = "Total Operating Expenses" Then GoTo Following
            If Cells(i, 1).Value = "Property Resale" Then GoTo Following
            If Cells(i, 1).Value = "Total Cash Flow" Then GoTo Following
                         
            Rows(i).Delete Shift:=xlUp
            i = i - 1

            Rows(9).RowHeight = 12.75
            
                            
            
               
    Following:
    Next

    DATABASE_LOC = "C:\Users\32948\Desktop\Cockpit\Cockpit"

    If IsFileOpen(DATABASE_LOC & "\Cashflow Appendix.xlsx") Then
    Windows("Cashflow Appendix.xlsx").Activate
    Else
    Set WbDatabase = Workbooks.Open(DATABASE_LOC & "\Cashflow Appendix.xlsx")
    End If

    WB_Appendix_Name = "Cashflow Appendix.xlsx"

    Windows(WB_Appendix_Name).Activate

    Columns("A:Z").Select
    Selection.Delete Shift:=xlUp


    Cells.Select
    Selection.Copy

    Windows(WB_Appendix_Name).Activate
    Cells.Select
    ActiveSheet.Paste


    ActiveWorkbook.Save
    ActiveWorkbook.Close

    Set WbDatabase = Nothing

    End With
     


    **Next WS**


    ActiveWorkbook.Save
    ActiveWorkbook.Close True
    Application.StatusBar = MyFile & " closed."
 
    'read next filename
    MyFile = Dir()
    Loop`

Your indentation is inconsistent.... fixing it would help spot the error. — BigBen, Oct 22 '20 at 17:09
Take a look at this link and see if you can apply the guid3lines there to your code: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Oct 22 '20 at 17:23

Tim Williams · Answer 1 · 2020-10-22T17:20:07.917

0

With Worksheets("Property Summary").Activate

the Activate method doesn't return anything you can use in your With block

? typename(activesheet.activate) '>>Boolean

And you're not using the WS from your outer loop.

edited Oct 22 '20 at 17:20

answered Oct 22 '20 at 17:12

Tim Williams

154,628
8
97
125

Thanks, Tim, interestingly this was present in the original code which seemed to work – LocaliCartel Oct 22 '20 at 17:16
1

It might not error but only because you never actually *use* that outer `With` – Tim Williams Oct 22 '20 at 17:19

score 0 · Answer 2 · answered Oct 22 '20 at 17:36

Were did you get the code you inserted from? I wasn't able to run just the insertion in sa sample macro, but this ran

Sub topborder()
Range("E26:H26").Select
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
    End With
End Sub

Receiving "Compile Error: Next without For" when attempting to insert new formatting instructions to existing code

2 Answers2