 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007Call a file name from within string
Similar to my last post, I want to be able to change a parameter within a calculation. I have tried to modify code that I used for a similar event from before but I am stumped. The code is as follows: - Dim extract As String ActiveCell.FormulaR1C1 = _ extract = InputBox("Please enter Version Control Master file", "File Name") ActiveCell.FormulaR1C1 = _ "=+'[& extract &]Static Summary'!R9C3-'[Version Control Master 20070815.xls]Static Summary'R9C3" Basically I am calling cell C9 from one sheet whose name changes daily and subtracting the value in C9 on the other sheet which does not change. Responses/guidance gratefully received. Thank you Mark One way: ActiveCell.FormulaR1C1 = _ "='[" & extract & "]Static Summary'!R9C3-'[Version Control Master 20070815.xls]Static Summary'R9C3" > ActiveCell.FormulaR1C1 = _ > "=+'[& extract &]Static Summary'!R9C3-'[Version Control Master > 20070815.xls]Static Summary'R9C3" Je, Tried your suggestion , comes up with a debug error. I also tried this: - ActiveCell.FormulaR1C1 = _ "='[" & extract & ".xls""]Static Summary'!R9C3-'[Version Control Master 20070815.xls]Static Summary but that just debugs too. Mark > One way: > [quoted text clipped - 10 lines] > > - Show quoted text - Not sure what 'debug' error you're getting - I'd expect an application error. Just noticed that you left out the last exclamation point. Try: ActiveCell.FormulaR1C1 = _ "='[" & extract & "]Static Summary'!R9C3-'[Version Control Master 20070815.xls]Static Summary'!R9C3" ='[Workbook1.xls]Static Summary'!R9C3-'[Version Control Master 20070815.xls]Static Summary'!R9C3 > Je, > Tried your suggestion , comes up with a debug error. [quoted text clipped - 23 lines] > > > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.