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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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,118,812
Messages
5,574,461
Members
412,595
Latest member
slim313
Top