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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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,101,957
Messages
5,483,893
Members
407,419
Latest member
Napoleao Paca

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top