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