How do I change the reference in a formula based on a logic test?
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,
|