VB Macro to Automate a Search/Replace

[Lotus123]

Okay – I might be able to do this myself, but I seriously doubt I coul
do it quickly or a very good job of it since my VB skills are on th
bottom rung of the ladder. If someone here is interested and has som
free time, then I would greatly appreciate a helpful hand!

I need/want a VB Macro that does the following:
1) Knows the current date, for example 6/07/06, and converts this dat
to 0606 (MMYY), but then subtracts 1 month to make it 0506. It als
knows the previous month is MAY (MMM).
2) Based on step 1, it knows the second previous month is 0406. Jus
like in step one, it also knows the second previous month is APR.
3) Using the range I would have selected prior to running the macro, i
does the following search and replace:

Before: 2006\APR 2006\[dp-0406
After: 2006\MAY 2006\[dp-0506

4) The macro would also need to update the year at the beginning whe
we changed to a new year.

So what is this for? I have about 50 reports I prepare for 5
different external companies in which I have to manually do a searc
and replace of the above formulas each month. It only takes about 1
seconds to do it on each report, but it is one of those things that
know could be easily automated with a macro that I could put in m
personal.xls workbook.

Thanks in advance for your help

 

[Guest]

just to illustrate from the immediate window:
? ucase(format(date,"yyyy\\mmm yyyy\\")) & format(date,"\[\d\p\-mmyy")
2006\JUN 2006\[dp-0606



Dim mydt as Date, dt(1 to 2) as Date, i as Long
Dim s1 as String, s2 as String
mydt = Date
for i = 1 to 2
dt(i) = DateSerial(year(date),month(dt)-i,1)
Next i
s1 = ucase(format(dt(1),"yyyy\\mmm yyyy\\")) & format(dt(1),"\[\d\p\-mmyy")
s2 = ucase(format(dt(2),"yyyy\\mmm yyyy\\")) & format(dt(2),"\[\d\p\-mmyy")
cells.Replace What:=s2, _
Replacement:=s1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

 

[mkerstei]

Try taking a look at this site
http://andrewsexceltips.blogspot.com/
...this might be another way to do things. Scroll down to th
heading:
June 12, 2004
The DATE function, Part 2

And see if that can do what you're looking for. If you want to turn i
into a macro, just record one.

ht

 

[Lotus123]

dt(i) = DateSerial(year(date),month(dt)-i,1)

Thanks Tom! When I run the macro I get the message:
Run-time error '13':
Type mismatch

When I run the debugger it stops on the above line

 

[Lotus123]

Try taking a look at this site
http://andrewsexceltips.blogspot.com/
...this might be another way to do things. Scroll down to th
heading:
June 12, 2004
The DATE function, Part 2

And see if that can do what you're looking for. If you want to turn i
into a macro, just record one.

hth

Thanks for the info - I'm familiar with Edate, and if I was just tryin
to update a date displayed in the spreadsheet, this would work; however
I'm updateing a path/filename reference...and I don't want tha
reference to change 3 or 4 months down the road when I open the fil
again - I just want to change it once.

I believe a macro is definately the way to go; however, the macro wil
need to be dynamic (I can't just record a macro that will work fo
every month). As such, I feel Tom's solution is the way to go thu
far

 

[Guest]

just a typo

DateSerial(year(date),month(dt)-i,1)

should be

DateSerial(year(date),month(date)-i,1)

so

Dim dt(1 to 2) as Date, i as Long
Dim s1 as String, s2 as String

for i = 1 to 2
dt(i) = DateSerial(year(date),month(date)-i,1)
Next i
s1 = ucase(format(dt(1),"yyyy\\mmm yyyy\\")) _
& format(dt(1),"\[\d\p\-mmyy")
s2 = ucase(format(dt(2),"yyyy\\mmm yyyy\\")) _
& format(dt(2),"\[\d\p\-mmyy")
cells.Replace What:=s2, _
Replacement:=s1, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

 

[Dave Peterson]

I think it was a simple typo:

dt(i) = DateSerial(year(date),month(dt)-i,1)
should be
dt(i) = DateSerial(year(date),month(date)-i,1)

 

[Lotus123]

just a typo

DateSerial(year(date),month(dt)-i,1)

should be

DateSerial(year(date),month(date)-i,1)

Thanks Tom! The VB Marco works perfectly!!!

Thanks a bundle for your A+ answer