Man, I am in DESPERATE need of a macro

[rasiel5]

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

 

[Tony Jollans]

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

 

[Jean-Guy Marcil]

(e-mail address removed) was telling us:
(e-mail address removed) nous racontait que :

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!

What else is there in the document? Hw will the macro differentiate these
code from what ever else is present in the document?

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[rasiel5]

....

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

 

[Tony Jollans]

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

 

[Greg Maxey]

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.

 

[Tony Jollans]

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.

--
Enjoy,
Tony

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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[rasiel5]

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.

--
Enjoy,
Tony

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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

- 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

 

[Tony Jollans]

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.

--
Enjoy,
Tony

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.

--
Enjoy,
Tony

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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"Tony Jollans" <My forename at my surname dot com> wrote in message
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

...

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:

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- Hide quoted text -

- 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

 

[rasiel5]

Seehttp://www.gmayor.com/installing_macro.htmfor help on how to use code
provided here.

I don't think we're (too) scary. Please do ask away with any questions.

--
Enjoy,
Tony

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.
--
Enjoy,
Tony

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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
"Tony Jollans" <My forename at my surname dot com> wrote in message
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
--
Enjoy,
Tony
...
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- Hide quoted text -
- 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- Hide quoted text -

- Show quoted text -

it did it! FANTABULOUS! I love you!!!

ras

 

[Tony Jollans]

Well done. I hope it saves you many hours of drudgery.

--
Enjoy,
Tony

Seehttp://www.gmayor.com/installing_macro.htmfor help on how to use code
provided here.

I don't think we're (too) scary. Please do ask away with any questions.

--
Enjoy,
Tony

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
ahead and did it! More flexible and, I'm sure, heaps faster. Much
better.



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.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"Tony Jollans" <My forename at my surname dot com> wrote in message
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

...

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:

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- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

it did it! FANTABULOUS! I love you!!!

ras