Automatically enter month in a cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Hi,
I get subscript out of range.
This is shown in yellow

Code:
With Sheets(arr(i))
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
My mistake,
Your pointer was correct then i looked and found a space.

Just thinking down th rd.
Should i wish to use this code for say multiple EXPENSES sheets, as opposed to writing the same thing say 6 times can you advise how to write it so its like,

Code:
arr = Array("INCOME (1)", "EXPENSES (1) [COLOR=#ff0000][B]THOUGH TO[/B][/COLOR] "EXPENSES (6)")
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
For the time it takes to write that 6 times just add them to the array. You only need to do it once. Probably less typing that this reply :)
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Thanks,was going to do that but thought about it wouldnt be clean.

Thanks for your time this afternoon
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
Morning,
Following on from the above my code now looks like this and works very well,thanks for your time yesterday.

Code:
Sub AddMonth()

Dim arr, i As Long


arr = Array("INCOME (1)", "INCOME (2)", "INCOME (3)", "EXPENSES (1)", "EXPENSES (2)", "EXPENSES (3)", "EXPENSES (4)", "EXPENSES (5)", "EXPENSES (6)", "EXPENSES (7)", "EXPENSES (8)")
For i = LBound(arr) To UBound(arr)
    With Sheets(arr(i))
        .Range("B1") = UCase(Format(Now, "mmmm"))
        .Range("B2") = Year(Now)
        With .Range("B1:B2")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            With .Font
                .Name = "Calibri"
                .FontStyle = "Bold"
                .Size = 11
            End With
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent1
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
        End With
    End With
Next


End Sub
In fact i like it so much i wish to now add to it.
The worksheet in question is called MILEAGE.

I am not sure how / where to add it into the code above.
Ive followed your advice from yesterday regarding recording macro etc & cleaned it up the the code below.

I now need some help / advice on merging it into the above code.
Many thanks.

Code:
        .Range("B1:C1") = UCase(Format(Now, "mmmm"))        .Range("D1") = Year(Now)
    With .Range("B1:C1:D1")
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
    With .Font
         .Name = "Calibri"
         .FontStyle = "Bold"
         .Size = 24
    End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
   With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
Its basically the same as before apart from,
Cell Range
Font Size
Worksheet name

Have a nice day.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
Just add this between the next and the end sub lines:

Code:
With Sheets("MILEAGE")
    .Range("B1:C1") = UCase(Format(Now, "mmmm"))
    .Range("D1") = Year(Now)
    With .Range("B1:D1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        With .Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 24
        End With
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End With
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,102,282
Messages
5,485,859
Members
407,522
Latest member
Tashbbb

This Week's Hot Topics

Top