 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / September 2007Man, I am in DESPERATE need of a macro
Could some kind soul please help? I need a macro that will shift a number range within a text string. The macro would ask me the text string (just one letter) and the starting number. When run it will perform a find and replace on the letter-number pair and bump up by number X all codes found in the document while leaving any codes outside of the range untouched. So, to use an axample: If I have these codes in a document: R001 R002 R003 R050 R066 R099 And my goal is to bump by one all codes higher than R025 by, say, 1 my end result after running the macro would be: R001 R002 R003 R051 R067 R100 This is for a numismatic project containing lists of these codes often running into the thousands which currently has to be done manually. I'm using Word 2003. I'll be happy to send an ancient coin to anyone kind enough to write me such a code! Ras rasiel@ dirty old coins.com I would test this but it's about what you want - it depends what else is in your document. It assumes upper case letters and three digit numbers. Letter = UCase(Left(InputBox("Enter Letter"), 1)) Number = InputBox("Enter starting Number") If MsgBox("You want to increment codes " & Letter & Format(Number, "000") & " and higher?", vbYesNo, "Confirm") = vbYes Then For CodeNum = 998 To Number Step -1 ActiveDocument.Range.Find.Execute FindText:=Letter & Format(CodeNum, "000"), _ ReplaceWith:=Letter & Format(CodeNum + 1, "000"), _ Replace:=wdReplaceAll Next CodeNum End If  SignatureEnjoy, Tony > Could some kind soul please help? > [quoted text clipped - 32 lines] > Ras > rasiel@ dirty old coins.com ... Thank you Tony (and Jean-Guy) - I'm *ecstatic* to hear this is at least possible. The code didn't work for me though, gave a compile error, but it's probably because I don't know what the hell I'm doing. I just opened a new macro and pasted it in and expected it to run. Obviously, you're talking to a rank spank newbie here ;-) Anyway, a working snippet of an actual document is here: http://www.dirtyoldbooks.com/temp/sample.doc You can see that each line has five of these codes. It happens very frequently that I need to "bump" one of the numbers in any of these four columns by one or two starting from any particular number. Like T100 all of a sudden I need to make T102 but, to ensure the codes stay matched, I need to manually go down the column and bump ALL codes by 2 startin with those that are T100 or higher. It's a way tedious thing to do manually and error prone, particularly when the lists go past a few hundred entries. I should buy stock in Excedrin! The ideal macro would ask me which of the four codes, always prefaced with letters B, O, R, T or M need to be bumped and by how many numbers. If it's hardwired to bump by one that's ok, i can always rerun it. Guys, I would be ETERNALLY grateful if you could walk me through this. I mean FOREVER lol It's probably line continuation problems due to the way the posts are reformatted. It's very rough and ready at the moment - and maybe quite slow - but you should be able to cut and paste now. When you have it, press Alt+F8 in your document and select it (IncrementCodes1) from the list and click Run. Good luck! Come back if you need more help. Sub IncrementCodes1() Letter = UCase(Left(InputBox("Enter Letter"), 1)) Number = InputBox("Enter starting Number") If MsgBox("You want to increment codes " _ & Letter & Format(Number, "000") & _ " and higher?", vbYesNo, "Confirm") = vbYes Then For CodeNum = 998 To Number Step -1 ActiveDocument.Range.Find.Execute _ FindText:=Letter & Format(CodeNum, "000"), _ ReplaceWith:=Letter & Format(CodeNum + 1, "000"), _ Replace:=wdReplaceAll Next CodeNum End If End Sub SignatureEnjoy, Tony > ... > [quoted text clipped - 24 lines] > Guys, I would be ETERNALLY grateful if you could walk me through this. > I mean FOREVER lol Tony, This may speed things up a bit: Set oRng = ActiveDocument.Range With oRng.Find .Text = pPrefix & "[0-9]{1,}" 'pPrefix is the starting letter .MatchWildcards = True While .Execute pTmp = Right(oRng, Len(oRng) - 1) If pTmp >= pNum Then 'pNum is the starting number pTmp = Format(Val(pTmp + Val(Me.ListBox1)), "00#") oRng.Text = pPrefix + pTmp oRng.Collapse wdCollapseEnd End If Wend End With It also expands useability to any number. SignatureGreg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. > It's probably line continuation problems due to the way the posts are > reformatted. [quoted text clipped - 47 lines] >> Guys, I would be ETERNALLY grateful if you could walk me through this. >> I mean FOREVER lol Thanks Greg. I was waiting to find out if it was going to be any good at all as it stood as Ras hadn't said what else was in the document. You just went ahead and did it! More flexible and, I'm sure, heaps faster. Much better. SignatureEnjoy, Tony > Tony, > [quoted text clipped - 67 lines] >>> Guys, I would be ETERNALLY grateful if you could walk me through this. >>> I mean FOREVER lol On Sep 28, 5:36 am, "Tony Jollans" <My forename at my surname dot com> wrote: > Thanks Greg. I was waiting to find out if it was going to be any good at all > as it stood as Ras hadn't said what else was in the document. You just went [quoted text clipped - 89 lines] > > - Show quoted text - some gremlins are conspiring against me here project methinks. i'm still wondering if I'm inserting the macro correctly. i look and look at the code which to my newbie eyes looks like some form of ancient nepalese script - i end up copying and pasting straight into the visual basic editor then running the macro. i replaced tony's macro (which gave me an invalid outside procedure error) with greg's which gave me "invalid use of Me keyword". was i supposed to append this somewhere or are both standalone macros? is there something wrong with my normal.dot file? not set up correctly? the document has some supporting text and a special font - nothing major as far as formatting or fancy styles. i'm not too concerned it'll screw with anything else in the document because i'm prepared to temporarily copy and paste just the codes to a new document, run the macro then cut and paste the results back into the main body. thank you guys for your continuing help. it took me months to work up the courage to come on here asking for help! ras See http://www.gmayor.com/installing_macro.htm for help on how to use code provided here. I don't think we're (too) scary. Please do ask away with any questions. SignatureEnjoy, Tony > On Sep 28, 5:36 am, "Tony Jollans" <My forename at my surname dot com> > wrote: [quoted text clipped - 120 lines] > > ras On Sep 28, 11:14 am, "Tony Jollans" <My forename at my surname dot com> wrote: > Seehttp://www.gmayor.com/installing_macro.htmfor help on how to use code > provided here. [quoted text clipped - 139 lines] > > - Show quoted text - it did it! FANTABULOUS! I love you!!! ras :-) Well done. I hope it saves you many hours of drudgery. SignatureEnjoy, Tony > On Sep 28, 11:14 am, "Tony Jollans" <My forename at my surname dot > com> wrote: [quoted text clipped - 158 lines] > > ras :-) rasiel5@gmail.com was telling us: rasiel5@gmail.com nous racontait que : > Could some kind soul please help? > [quoted text clipped - 29 lines] > I'm using Word 2003. I'll be happy to send an ancient coin to anyone > kind enough to write me such a code! What else is there in the document? Hw will the macro differentiate these code from what ever else is present in the document? SignatureSalut! _______________________________________ Jean-Guy Marcil - Word MVP jmarcilREMOVE@CAPSsympatico.caTHISTOO Word MVP site: http://www.word.mvps.org
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.