Input function into cell VBA help

[nshepo20]

Hello, thank you for the generous help. I am trying to input the following functions into the corresponding cell range. Unfortunately, I get syntax error on the formula even though it is inside of quotations and should be read as a string (or at least I thought). The Error highlights "filename" on the first line and "f" in the third line. The middle line works. Any idea how the syntax would work for this? Thanks

Sub Dateformat()

Sheets("Combined").range("A1").formula = "=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)"
Sheets("Combined").Range("B1").Formula = "=RIGHT(YEAR(TODAY()),2)"
Sheets("Combined").Range("A3:A" & .Range("B" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE(MID($A$1,FIND("f",$A$1,3)+2,(FIND("v",$A$1,1))-(FIND("f",$A$1,3)+3)),".",$B$1)"

End Sub

 

[Fluff]

You need to double-up the quotes inside the formula like

Sheets("Combined").range("A1").formula = "=MID(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1),1)+1,FIND(""]"",CELL(""filename"",A1),1)-FIND(""["",CELL(""filename"",A1),1)-1)"

 

[Joe4]

The issue occurs because double-quotes are using in VBA as text qualifiers, but they are also used in your formulas as literal values, so it requires doubling up of double-quotes, which can be a bit confusing.

The easiest thing to do, in my opinion, is to let Excel figure it out for you!
Simply turn on your Macro Recorder, and enter the formula you want on the sheet (i.e. in cell A1). Then stop the Macro Recorder and view the code.
Copy and paste the formula part to your code to get what you need.

 

[nshepo20]

Thank you @Fluff !

 

[nshepo20]

The issue occurs because double-quotes are using in VBA as text qualifiers, but they are also used in your formulas as literal values, so it requires doubling up of double-quotes, which can be a bit confusing.

The easiest thing to do, in my opinion, is to let Excel figure it out for you!
Simply turn on your Macro Recorder, and enter the formula you want on the sheet (i.e. in cell A1). Then stop the Macro Recorder and view the code.
Copy and paste the formula part to your code to get what you need.

@Joe4 Thank you for the explanation, that makes much more sense and thank you for the tip, I'll certainly give that a try for next time

 

[Fluff]

Glad we could help & thanks for the feedback.