Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Search and Replace | Excel Discussion (Misc queries) | |||
Variable in a Search and Replace macro | Excel Programming | |||
macro to search and replace with offset | Excel Discussion (Misc queries) | |||
Macro to Search and Replace | Excel Programming | |||
SEARCH & REPLACE MACRO | Excel Programming |