Microsoft visual basic: Compile error expected end with

jjxxoo · #1 12-01-2022, 12:49 PM

MICROSOFT VISUAL BASIC: COMPILE ERROR EXPECTED END WITH
I am expected to fix this code but am unsure how i keep rearranging things but get error codes and it wont let me run the code. If someone can help please.

Code:

Sub Formatting()
 
 ' Objective
 ' Name
 ' Date
 
    With Worksheets("Formatting")
        
        With .Range("A1")
            .Value = "Expenses For March"
            With .Font
                .Name = "Arial"
                .Bold = True
                .ColorIndex = 5
                .Size = 14
         End With
         
          With .Interior
                .ColorIndex = 2
            End With
            
            .HorizontalAlignment = xlLeft
        End With
        
        With Range("A3:A6")
            .InsertIndent 1
            With .Font
                .Italic = True
                .Bold = True
                .ColorIndex = 3
            End With
            
            With .Range("B3:B6")
                .Interior.ColorIndex = 37
                .NumberFormat = "$#,##0"
            End With
        
        End With 

End Sub

macropod · #2 12-01-2022, 11:57 PM

Not only are you missing the required final 'End With', your 'With .Range("B3:B6") ... End With' block is logically inside your 'With Range("A3:A6") ... End With' block. All this would be readily apparent if you structured your code more consistently and avoided 'With ... End With' blocks containing only one element. For example:

Code:

Sub Formatting()
With Worksheets("Formatting")
    With .Range("A1")
        .Value = "Expenses For March"
        With .Font
            .Name = "Arial"
            .Bold = True
            .ColorIndex = 5
            .Size = 14
        End With
        .Interior.ColorIndex = 2
        .HorizontalAlignment = xlLeft
    End With
    
    With .Range("A3:A6")
        .InsertIndent 1
        With .Font
            .Italic = True
            .Bold = True
            .ColorIndex = 3
        End With
    End With
    
    With .Range("B3:B6")
        .Interior.ColorIndex = 37
        .NumberFormat = "$#,##0"
    End With
End With
End Sub

Your 'With Range("A3:A6") ... End With' block should also be 'With .Range("A3:A6") ... End With'.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help fixing compile error expected End Sub to combine multiple documents into one (Mac)	Vivianweir	Excel Programming	3	11-02-2018 02:20 AM
Microsoft visual basic for applications - errors occurred during load.	aditya1810	Excel Programming	0	06-05-2018 08:37 AM
compile error expected end sub	ashg75	Word VBA	2	07-13-2017 07:08 AM
Word Visual Basic error - run time error 504	crazymorton	Word	11	01-13-2012 04:32 AM
A little Visual Basic Help Please	leroytrolley	Excel	4	08-22-2008 03:57 AM