How do I change the reference in a formula based on a logic test?
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, |
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, |
How do I change the reference in a formula based on a logic te
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, |
How do I change the reference in a formula based on a logic te
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, |
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, |
All times are GMT +1. The time now is 09:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com