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

 

[Misca]

Try replacing the semicolons with commas:

Range("A2").Formula = "=MID(CELL(""filename""),FIND(""."",CELL(""filename""))-1,1)"

 

[p45cal]

This looks like it should be returning the single character before the first '.' in the filename. Is that what you intend?

It works here ok if I replace the ;s (colons) with ,s (commas):

ActiveCell.FormulaR1C1 = "=MID(CELL(""filename""),FIND(""."",CELL(""filename""))-1,1)"

Maybe it's that?
What's the error you get? 1004?

 

[Feenstra]

Easy! That's it. Thanks so much!

 

[john456852]

I need to insert function: "=IF(OR(WEEKDAY(C6)=7,WEEKDAY(C6)=1)"1","")"
i Tried this: range("D6").Formula = "=IF(OR(WEEKDAY(C6)=7,WEEKDAY(C6)=1)"1","")"
, but it says that there is an error and this ^ is supposed to be the end of the statement

 

[mmmarks]

Hi,may be ..

range("D6").Formula = "=IF(OR(WEEKDAY(C6)=7,WEEKDAY(C6)=1),"1","")"
just add comma there.

 

[Jonmo1]

Also need to double up the quotes..

range("D6").Formula = "=IF(OR(WEEKDAY(C6)=7,WEEKDAY(C6)=1),""1"","""")"