Input function into cell VBA help

nshepo20

New Member
Joined
Jun 8, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
You need to double-up the quotes inside the formula like
VBA Code:
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)"
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,962
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 8, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,203
Messages
5,704,924
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top