Macro to Enter formula into Cell

nuver

New Member
Joined
Nov 14, 2005
Messages
1
Hello
I am attempting to have a macro enter the formulas below into cells "B1" and "B2" of the active worksheet but I am receiving a compile error with the statement "Expected end of statement". Not sure what I am doing wrong but any help would be appreciated.

Range("b1").Formula = "=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)"

Range("b2").Formula = "=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)"

What I am attempting to do is to convert hundreds of inherited files that have been saved under the format Excel 5.0/95 to the standard Excel workbook format. All files are linked to external spreadsheets and I simply need the information not the formulas. I have attached a macro to my personal macro workbook that executes from a toolbar to value out each spreadsheet.

I may be way off base and if there is an easier solution please let me know. I beleive if I were to create a macro to insert a worksheet and copy the above mentioned formulas into the dsignated cells of the new sheet and then value the information out, I would have the current filename. My next step would be to save the file to my desktop as "delete.xls". I should them be able to save the file as the old filename,(referencing the values from the formulas), in the Excel Workbook format and overriide the older format. Last step of the macro would be to delete the added spreadsheet.

I think this should work if I could just get the code to enter the formulas above.

Thank for any help or direction
Ed
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Ed

Try
Code:
Range("b1").Formula = "=LEFT(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1),1)-1)"

Range("b2").Formula = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1,SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"

Tony
 

smi123

Board Regular
Joined
Nov 7, 2005
Messages
67
Use . value not .formula
Mine always work with .value when using cell references and not Row Column References.

Also, you may have to double up on your quotation marks where you need quotation marks within the formula. For example if testing for a blank you would need """" for the formula to work

Range("b1").value = "=LEFT(CELL("filename",A1),FIND(""["",CELL("filename",A1),1)-1)"

I do not understand the "Filename" part or what you have in A1, so trust this helps
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Code:
Public Sub DEMO()

    ' Get Path
    Range("B1").Value = ThisWorkbook.Path
    
    ' Get File Name
    Range("B2").Value = ThisWorkbook.Name

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,358
Messages
5,601,149
Members
414,431
Latest member
JustmemyselfandI

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