VBA Macro to Convert dates to “MMM” with VBA and paste as values

Question

I have a column in Excel that contains dates. Normally, I add a column next to it and use the formula =TEXT(A1,"MMM") to get the month abbreviation like “FEB”. How can I do the same with VBA and then paste the results as values (no formulas)?

expect to find faster and easir way

Iterate the range and use FORMAT: Range("A1") = Format(Range("A1"),"MMM") — Scott Craner
– Scott Craner, Commented Sep 2 at 21:05
Thank u, if the coulome have 100 cell how can apply them all not only A1 — Shahd Basaleh
– Shahd Basaleh, Commented Sep 2 at 21:17

Scott Craner · Accepted Answer · 2025-09-02 21:23:27Z

2

Use a Loop and Format:

Sub ChangeIt()

Dim rng as Range
Set rng = ActiveSheet.Range("A1:A100")

Dim rngArr as variant
rngArr = rng

Dim i as Long
For i = LBound(rngarr,1) to Ubound(rngarr,1)
    rngarr(i,1) = Format(rngarr(i,1),"MMM")
Next i

rng.NumberFormat = "General"
rng = rngarr

End Sub

answered Sep 2 at 21:23

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Macro to Convert dates to “MMM” with VBA and paste as values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related