Formula in excel table stays as text

[Costas]

Hi,

I've got this excel table created through Jet reports and I've added a column at the far right. I'm now trying to insert the formula below but when I do that, it comes out as text. I've checked the format before running the code and it's "dd/mm/yy" and after I run the code it changes to text. Even when I manually change it back to the date format, the table still shows the formula as text. It's only when I press F2 and Enter that it converts to a date format.

X = MyObject.ListColumns.Count
    MyObject.HeaderRowRange.Cells(1, X + 1).Value = "MMM-YY"
    MyObject.DataBodyRange.Columns(9).FormulaR1C1 = _
        "=date(year([@[Date]]);month([@[Date]]);1)"

So my question is in two parts:
a) How can I fix my code; and
b) Is there an easier way to change from text to date other than F2 and Enter? (BTY the little dot to convert from txt to number is not there)

Thanks
Costas

 

[NARAYANK991]

Hi Costas ,

Try this :

Public Sub tmp()
          Dim myobject As ListObject
          Set myobject = ActiveSheet.ListObjects(1)
          With myobject
                X = .ListColumns.Count
                .HeaderRowRange.Cells(1, X + 1).Value = "MMM-YY"
                .DataBodyRange.Columns(.Range.Columns.Count).Formula = "=date(year(" & .Name & "[[#This Row],[Date]]),month(" & .Name & "[[#This Row],[Date]]),1)"
          End With
End Sub

You will have to change the separators to suit your system ; mine uses the comma ",".

Narayan

 

[Costas]

Nice one! Thanks Narayan