Help with VBA in Excel (Date Formatting) 1

[TanmedIT]

I am trying to populate 2 fields with formulas for month and year. I can get them to use the numeric value for month, but I would like it to show the "mmm" format, but change the format in the VBA code rather than having to format the cells in excel.

Here is my current code that makes the months numeric:

Sub MonthData()
Dim MRow As Long
Worksheets("Customer Sales").Activate
CRow = Range("I65536").End(xlUp).Row
Range("I2:I" & CRow).Clear
Range("J2:J" & CRow).Clear
MRow = Range("A65536").End(xlUp).Row
Range("I2").Formula = "=Month(B2)"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & MRow), Type:=xlFillDefault
Range("I2:I" & MRow).Copy
Range("I2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("J2").Formula = "=Year(B2)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & MRow), Type:=xlFillDefault
Range("J2:J" & MRow).Copy
Range("J2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Worksheets("Distributor Input and Pivot").Activate
ActiveWorkbook.RefreshAll
End Sub


But when I change Range("I2").Formula = "=Month(B2)"
to
Range("I2").Formula = "=Text(B2,"mmm")" I get a debug error.

Any help would be appreciated

 

[PHV]

What about this ?
Range("I2").Formula = "=Text(B2,""mmm"")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TanmedIT]

Sometimes you just need a second set of eyes...

Dang that was dumb of me... I knew I was close..

Thanks