CODEWITHSUNDEEP
vbaexcel
Gandalfrandalf
Gandalfrandalf

Reputation: 257

how to generate the month & year in vba as text (has to be in text because using the match function)

Does anyone know how I could write code that will do the following:

As text to have in cells A1:Z1 today's month and year onward

So I would have 12/2017 in A1 and 01/2018 in B1, 02/2018 in C1 etc... It is very important that the '0' is in cell B1 and that they are in text format

I think this would be done in a loop, e.g.

for each mycell in range("A1:Z1")
mycell.numberformat = "@"
Next

What I am not sure how to do is to get the 09 to change to 10.

The reason That I am doing it like this is because I am calling the match function and concatenating

VBA.DateTime.month(Date) & "/" & VBA.DateTime.year(Date)

to find the matching cell

Upvotes: 2

Views: 2494

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

How about:

Sub FillInTheDate()
    Dim r As Range, i As Long
    i = 12
    For Each r In Range("A1:Z1")
        r.NumberFormat = "@"
        r.Value = Format(DateSerial(2017, i, 1), "m/yyyy")
        i = i + 1
    Next r
End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Try this in an empty workbook:

Option Explicit

Public Sub TestMe()

    Dim rngCells    As Range
    Dim cnt         As Long

    For Each rngCells In Range("A1:A10")
        cnt = cnt + 1
        rngCells.NumberFormat = "@"
        rngCells = Format(DateSerial(2017, cnt, 1), "MM\/YYYY")
    Next rngCells

End Sub

You would get this:

enter image description here

As mentioned in the comments by @JohnyL, in this case you need an escaping symbol for the / symbol in the format.

If you ever forget about the escaping symbol and similar stuff, the macro recorder is really a valuable & underestimated tool in Excel. See the recorded code:

Sub Macro1()
    ActiveCell.FormulaR1C1 = "13-Jan-2017"
    Range("A12").Select
    Selection.NumberFormat = "dd\/mm\/yyyy"
End Sub

Upvotes: 1

Related Questions