CODEWITHSUNDEEP
excelvba
Filip Ondo
Filip Ondo

Reputation: 165

Auto Fill Month in blank cells

I have question how to improve my macro to go trough column A and when its find blanks cell then it will type there name of month? I tried something like in below but its only work with Range("A1").value instead of Cells(Rows.Count, "A").End(xlUp).Row

 Sub actual_month()
'fill month col

Dim arrMonths() As String
ReDim arrMonths(1 To 12)

Application.DisplayAlerts = False
arrMonths(1) = "JAN"
arrMonths(2) = "FEB"
arrMonths(3) = "MAR"
arrMonths(4) = "APR"
arrMonths(5) = "MAY"
arrMonths(6) = "JUNE"
arrMonths(7) = "JULY"
arrMonths(8) = "AUG"
arrMonths(9) = "SEP"
arrMonths(10) = "OCT"
arrMonths(11) = "NOV"
arrMonths(12) = "DEC"

Workbooks("UAC_report_p.xlsb").Activate

Sheets("SLA Calculation").Select


For Each Cell In ActiveSheet.UsedRange.Cells
      'do some stuff
   Next



For i = 1 To 12



Cells(Rows.Count, "A").End(xlUp).Row = Month(Date)
  If Cells(Rows.Count, "A").End(xlUp).Row.Value = Month(Date) Then _
     Cells(Rows.Count, "A").End(xlUp).Row.Value = arrMonths(Cells(Rows.Count, "A").End(xlUp).Row.Value)
   Next i




Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 590

Answers (1)

Sam
Sam

Reputation: 7303

Assuming you're using the current month, you can do it like this

Public Sub test()
    Dim endrow As Long
    Dim ws As Worksheet
    Dim Col As Long
    Dim arrMonths(1 To 12)

    arrMonths(1) = "JAN"
    arrMonths(2) = "FEB"
    arrMonths(3) = "MAR"
    arrMonths(4) = "APR"
    arrMonths(5) = "MAY"
    arrMonths(6) = "JUNE"
    arrMonths(7) = "JULY"
    arrMonths(8) = "AUG"
    arrMonths(9) = "SEP"
    arrMonths(10) = "OCT"
    arrMonths(11) = "NOV"
    arrMonths(12) = "DEC"

    Set ws = Sheet4

    'set column to "A"
    Col = 1


    endrow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row + 1

    If ws.Cells(endrow, Col).Value = "" Then _
    ws.Cells(endrow, Col).Value = arrMonths(Month(Now))
End Sub

EDIT

A better way would be not to use the Arrays at all and use the LanguageID to get the Equivalent of the Month Name in a particular Language. For a complete list of LanguageID's, please refer to THIS The LanguageID for English_United_States is 409

Try this code

Public Sub test()
    Dim ws As Worksheet
    Dim Lrow As Long, Col As Long
    Dim sMonth As String

    Set ws = Sheet4

    '~~> Not sure if Czech has same names as `Text` / `Today`. 
    '~~> If not then replace `Text` and `Today` with their respective equivalents.
    sMonth = Application.Evaluate("=TEXT(TODAY(),""[$-409]MMM"")")

    With ws
        '~~> Set column to "A"
        Col = 1

        Lrow = .Cells(.Rows.Count, Col).End(xlUp).Row + 1

        If .Cells(Lrow, Col).Value = "" Then _
        .Cells(Lrow, Col).Value = sMonth
    End With
End Sub

Upvotes: 1

Related Questions