Excel VBA Error - End With (Resolved)

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Excel VBA Error - End With (Resolved)

Post by Neib74656 » Sun Jul 17, 2022 7:16 am

Hello Everyone,

I am trying to get MS to run some VBA to set the cell outlines to black. However, Excel gives an error "expected end with" and then the macro fails.

Here is the code for what I am trying to work with.

Code: Select all

labeltovar>vba_code3,theCode3
XLRunCode>A16,theCode3

/*
vba_code3:
Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
*/
As always any help is appreciated!

Thanks!

Neib74656
Junior Coder
Posts: 29
Joined: Fri Sep 10, 2021 10:51 pm

Re: Excel VBA Error - End With (Resolved)

Post by Neib74656 » Sun Jul 17, 2022 7:25 am

Sorry for cluttering up the board uselessly. The issue was that an End With was missing in my code.

original section of code with error

Code: Select all

With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    
 //End With was missing here!!!!
 
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts