Automatically enter month in a cell
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Automatically enter month in a cell

  1. #11
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,317
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically enter month in a cell

    Hi,
    I get subscript out of range.
    This is shown in yellow

    Code:
    With Sheets(arr(i))
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #12
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,486
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Automatically enter month in a cell

    Then either INCOME(1) or EXPENSES(1) sheet doesnt exist.

  3. #13
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,317
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically enter month in a cell

    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) THOUGH TO "EXPENSES (6)")
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #14
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,486
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Automatically enter month in a cell

    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

  5. #15
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,317
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically enter month in a cell

    Thanks,was going to do that but thought about it wouldnt be clean.

    Thanks for your time this afternoon
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #16
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,317
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically enter month in a cell

    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.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #17
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,486
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Automatically enter month in a cell

    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

  8. #18
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,317
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically enter month in a cell

    Spot on many thanks.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •