VB Macro to Automate a Search/Replace
 

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:

Befo 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

--
Lotus12
-----------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=54951

VB Macro to Automate a Search/Replace
 

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

--
Regards,
Tom Ogilvy


"Lotus123" wrote:


Okay €“ I might be able to do this myself, but I seriously doubt I could
do it quickly or a very good job of it since my VB skills are on the
bottom rung of the ladder. If someone here is interested and has some
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 date
to 0606 (MMYY), but then subtracts 1 month to make it 0506. It also
knows the previous month is MAY (MMM).
2) Based on step 1, it knows the second previous month is 0406. Just
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, it
does the following search and replace:

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

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

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

Thanks in advance for your help!


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=549518

VB Macro to Automate a Search/Replace
 

Tom Ogilvy Wrote:


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

--
Lotus12
-----------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=54951

VB Macro to Automate a Search/Replace
 

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

--
regards,
Tom Ogilvy


"Lotus123" wrote:


Tom Ogilvy Wrote:


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
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=549518

VB Macro to Automate a Search/Replace
 

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 wrote:

Tom Ogilvy Wrote:


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
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=549518

--

Dave Peterson

VB Macro to Automate a Search/Replace
 

Tom Ogilvy Wrote:
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

--
Lotus12
-----------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...fo&userid=2861
View this thread: http://www.excelforum.com/showthread.php?threadid=54951