How do I change the reference in a formula based on a logic te
That took care of the problem. Thanks to both of you.
"Gord Dibben" wrote:
Carl
Those two lines are actually all one line.
Enter a line-continuation character and try again..
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName & _
Mid(myFormula, InStr(1, myFormula, "]"))
Gord Dibben MS Excel MVP
On Wed, 2 Jul 2008 13:20:01 -0700, Carl wrote:
Thanks, Bernie,
I think you are very close but I got a syntax error on the following steps
(turned red) and could not figure out how to correct it:
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
Thanks again, Carl
"Bernie Deitrick" wrote:
With the activecell as the cell with the formula
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
Sub ChangeFileNameTest()
Dim myFormula As String
Dim myFName As String
myFormula = ActiveCell.Formula
myFName = "MTG Source File " & Day(Date) & ".xls"
myFormula = Left(myFormula, InStr(1, myFormula, "[")) & myFName &
Mid(myFormula, InStr(1, myFormula, "]"))
MsgBox "Here's the new formula: " & myFormula
ActiveCell.Formula = myFormula
End Sub
Of course, the logic I used for the myFName wasn't based on anything other
than a wild guess....
HTH,
Bernie
MS Excel MVP
"Carl" wrote in message
...
I need the formula to change the name of the source file based on a logic
test. For examle, how would I change the following so that it references a
different file name based on the day of the month?
='C:\[MTG Source File 1.xls]Sheet1'!$A$1
I know how to get the day of the month, just haven't been able to get the
name of the file to change in the formula without a reference error.
Thanks
so much in advance,
|