Find & Replace w/ Name Changes

[ronbo]

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

More specifically, I am looking for a way to create a
macro using "Find & Replace" that will find and replace
the month name. So I have;

Cells.Select
Selection.Replace What:="Month(Now()-1",
Replacement:="Month (Now()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Obviously it does not work. I need to know how to create
the "What:=" and the "Replacement:=" to -
What:= "last month (Jun)"
Replacement:= this month (Jul)

Thanks to Tom Ogilvy for the solution using "Indirect",
however the workbook that the data is generated from is
never open, but it is always in the same directory.


Thanks for any additional help.

 

[Tom Ogilvy]

Changing the formula with replace would be the "goofy" way to do it.

Turn on the macro recorder and then do

Edit=>Links=>Change Source and change the source to "new" last month's
workbook.

That will give you the code you need. You can then modify it to make it
dynamic.

to format the date to match your file naming convention

Sub Tester2()
Dim dt As Date, oldDt As Date
Dim oldoldDt As Date
Dim sOldMonthandYear As String
dt = Date
oldDt = DateSerial(Year(dt), Month(dt) - 1, 1)
oldoldDt = DateSerial(Year(oldDt), Month(oldDt) - 1, 1)
sCurrentLinkName = "Report " & Format(oldoldDt, "mmm yy") & ".xls"
sOldMonthandYear = "Report " & Format(oldDt, "mmm yy") & ".xls"
Debug.Print dt, oldDt, oldoldDt
Debug.Print sCurrentLinkName, sOldMonthandYear
End Sub

produces:
7/20/2004 6/1/2004 5/1/2004
Report May 04.xls Report Jun 04.xls

So the current link is to the file named for two months ago and you want to
change it to the file name for one month ago.

 

[Bryan Kelly]

I am quite new a writings macros, but this is what I developed to do a bunch
of name changes.

Sub Some_Other_Sub
Call Change_one_cell_value("slaveCmd1", "AZslave")
Call Change_one_cell_value("slaveCmd2", "ELslave")
End Sub


Sub Change_one_cell_value(find_target As String, new_value As String)
' Find a value in a cell and replace it.
' Note that this does no cell selections, it does not
' change the currently selected cell.
'
Set Search_Range = ActiveSheet.Range("1:1").Find(find_target)

If Not (Search_Range Is Nothing) Then
Search_Range.Formula = new_value
End If

End Sub

Use a subroutine, don't re-write the code each time you need it.
Keep it simple.
Please let me know if you find this useful or if you have any sugestions.

Bryan

 

[ronbo]

-----Original Message-----
I am quite new a writings macros, but this is what I developed to do a bunch
of name changes.

Sub Some_Other_Sub
Call Change_one_cell_value("slaveCmd1", "AZslave")
Call Change_one_cell_value("slaveCmd2", "ELslave")
End Sub


Sub Change_one_cell_value(find_target As String, new_value As String)
' Find a value in a cell and replace it.
' Note that this does no cell selections, it does not
' change the currently selected cell.
'
Set Search_Range = ActiveSheet.Range("1:1").Find (find_target)

If Not (Search_Range Is Nothing) Then
Search_Range.Formula = new_value
End If

End Sub

Use a subroutine, don't re-write the code each time you need it.
Keep it simple.
Please let me know if you find this useful or if you have any sugestions.

Bryan

I have a workbook that I use month to month named "Report
MTH YY" This month the report is named "Report Jul 04".
Next month the report will be renamed "Report Aug 04",
then "Report Sep 04" etc.

I have formulas that reference last months report. As
simple as " ='[REPORT Jun 04.xls]Sheet 1'!A1". Next
month in "Report Aug 04" I want the formula to reference
the Jul report i.e. ='[REPORT Jul 04.xls]Sheet 1'!A1" .

I am looking for a way to automate the process rather
than manually using "Find & Replace", but I do not know
how to get the month names to change automatically.

More specifically, I am looking for a way to create a
macro using "Find & Replace" that will find and replace
the month name. So I have;

Cells.Select
Selection.Replace What:="Month(Now()-1",
Replacement:="Month (Now()", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Obviously it does not work. I need to know how to create
the "What:=" and the "Replacement:=" to -
What:= "last month (Jun)"
Replacement:= this month (Jul)

Thanks to Tom Ogilvy for the solution using "Indirect",
however the workbook that the data is generated from is
never open, but it is always in the same directory.


Thanks for any additional help.

.
Bryan

Thanks a lot for the response and your time. I am
starting by using Tom's suggestion and hopefuly I will
get what I need. If not I will try yours.

Again, thanks. I and I am sure all others who use this
forum truly appreciate your help and all of the others who
help.