Macro to insert excel function in cell

[Feenstra]

Hey there,

I would like my macro to insert a function into a cell. The function works in Excel but if I use the function in my macro it gives an error. Any idea how to write it correctly?

Sub Prepare_data()

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Version"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=MID(CELL(""filename"");FIND(""."";CELL(""filename""))-1;1)"

LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A2").AutoFill Destination:=Range("A2:A" & LastRow)

End sub

 

[bertie]

Replace the semi colons with commas (,)

 

[Feenstra]

That's it! Thanks so much!

 

[fimakr]

Could you please provide code to replace the value in the cell G1 (value is 'Jan') with the function : =DATE(YEAR(TODAY()),1,1)
and values in cells H1:AD1 with function =DATE(YEAR(G1),MONTH(G1)+1,DAY(G1))
Thank you in advance

 

[bertie]

Try this, edit sheet name.

[COLOR=darkblue]Sub[/COLOR] InsertDates()
   [COLOR=darkblue]With[/COLOR] Sheets("[COLOR=red]Sheet1[/COLOR]")
      .Range("G1").Formula = "=DATE(YEAR(TODAY()),1,1)"
      .Range("H1:AD1").Formula = "=DATE(YEAR(G1),MONTH(G1)+1,DAY(G1))"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[fimakr]

Thanks a lot, it's so easy when EXPERT helps. It worked

 

[Scott Huish]

Why formulas?

Sub InsertDates()
   With Sheets("Sheet1")
      .Range("G1") = DateSerial(Year(Now), 1, 1)
      .Range("G1:AD1").DataSeries Type:=xlChronological, Date:=xlMonth
   End With
End Sub

 

[bertie]

Why formulas?

Sub InsertDates()
   With Sheets("Sheet1")
      .Range("G1") = DateSerial(Year(Now), 1, 1)
      .Range("G1:AD1").DataSeries Type:=xlChronological, Date:=xlMonth
   End With
End Sub

Following on from the OP. Didn't realise it was a different guy.