How to loop a macro in a word document.

M

Muppet

Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave
 
M

Muppet

Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do numbers
above 9 and goes to three decimal places, so now it looks like this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others, so
when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number in
the line or am I being particularly stupid and missing something obvious in
the equation?



Regards



Dave


Graham Mayor said:
You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of the
document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others, so
when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number in
the line or am I being particularly stupid and missing something obvious
in the equation?



Regards



Dave


Graham Mayor said:
You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of the
document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
M

Muppet

Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a question
for this group.


Thanks once again for your valuable help.


Dave

Graham Mayor said:
Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others, so
when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number
in the line or am I being particularly stupid and missing something
obvious in the equation?



Regards



Dave


Graham Mayor said:
You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of
the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

What you ask is certainly possible - and in Word - by using a macro, but
before making a suggestion, I would need to know what is used to create the
space between the first and second values. Is it a tab character or is it a
sequence of spaces. This message format shows it as non breaking spaces, but
that seems unlikely in the real document. Can you send me a sample document
to the link on the home page of my web site?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Graham Mayor said:
Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others,
so when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number
in the line or am I being particularly stupid and missing something
obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of
the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

Working on the premise that the space is made by a tab character and having
nothing better to do on a very hot Sunday afternoon - the following might
work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Graham Mayor said:
Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others,
so when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number
in the line or am I being particularly stupid and missing something
obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of
the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
M

Muppet

Glad your enjoying a warm summer's day :)


Thanks for your quick reply. I think if I give you the website address it
would be easier for you to get the information you need.


www.supernovabots.com/prices_0.txt


This is a list used in an online game, please ignore the names to the right
of the list I have a small script that removes them.


Thanks for your help


Dave


Graham Mayor said:
Working on the premise that the space is made by a tab character and
having nothing better to do on a very hot Sunday afternoon - the following
might work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value
missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Graham Mayor said:
Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others,
so when I run it using the lines I have given as an example it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole number
in the line or am I being particularly stupid and missing something
obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of
the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

It is unpleasantly hot - mid thirties and very high humidity :( I'm off to
the mountains to cool down. When I get back I'll fix the macro to work with
your document


Muppet said:
Glad your enjoying a warm summer's day :)


Thanks for your quick reply. I think if I give you the website address it
would be easier for you to get the information you need.


www.supernovabots.com/prices_0.txt


This is a list used in an online game, please ignore the names to the
right of the list I have a small script that removes them.


Thanks for your help


Dave


Graham Mayor said:
Working on the premise that the space is made by a tab character and
having nothing better to do on a very hot Sunday afternoon - the
following might work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value
missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based
upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks like
this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not others,
so when I run it using the lines I have given as an example it
becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole
number in the line or am I being particularly stupid and missing
something obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end of
the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

OK - that was much more complicated than your messages suggested so far :(

Start with a document containing a single column table and put the letters
from the square brackets that you wish to process in the cells of the table
(omitting the square brackets). Save the document and put the complete path
where indicated in the following macro.

The number sequences do not all have the same number of characters and would
throw out the alignment of the text if they were processed as you originally
suggested. The following macro keeps the alignment, but inevitably this will
mean spaces in the number groups. With a document as large as your sample,
it will take a while to run:

Dim oChanges As Document, oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim oPara As Range
Dim rFindText As Range
Dim sFind As String
Dim sLow As String
Dim sHigh As String
Dim i As Long
Dim sFname As String
'**************************************
sFname = "D:\My Documents\Test\Codes.docx"
'**************************************
Set oDoc = ActiveDocument
Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
Set oTable = oChanges.Tables(1)
For i = 1 To oTable.Rows.Count
Set oRng = oDoc.Range
Set rFindText = oTable.Cell(i, 1).Range
rFindText.End = rFindText.End - 1
sFind = "\[" & rFindText & "\]"
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(findText:=sFind, _
MatchWholeWord:=True, _
MatchWildcards:=True, _
Forward:=True, _
Wrap:=wdFindStop) = True
Set oPara = oRng.Paragraphs(1).Range
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + 5
sLow = Trim(oPara.Text)
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + 5
sHigh = Trim(oPara.Text)
If sLow = "" Then
sLow = Format(sHigh - (sHigh * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + Len(sLow)
oPara.Text = sLow
End If
If sHigh = "" Then
sHigh = Format(sLow + (sLow * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + Len(sHigh)
oPara.Text = sLow
End If
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 55
oPara.End = oPara.Start + 6
oPara.Text = ",0,"
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 62
oPara.End = oPara.Start + 2
oPara.Text = ",0"
Loop
End With
Next i
oChanges.Close wdDoNotSaveChanges

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



Graham Mayor said:
It is unpleasantly hot - mid thirties and very high humidity :( I'm off to
the mountains to cool down. When I get back I'll fix the macro to work
with your document


Muppet said:
Glad your enjoying a warm summer's day :)


Thanks for your quick reply. I think if I give you the website address it
would be easier for you to get the information you need.


www.supernovabots.com/prices_0.txt


This is a list used in an online game, please ignore the names to the
right of the list I have a small script that removes them.


Thanks for your help


Dave


Graham Mayor said:
Working on the premise that the space is made by a tab character and
having nothing better to do on a very hot Sunday afternoon - the
following might work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value
missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the second
value is missing) or 25% lower (if the first value is missing) based
upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks
like this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not
others, so when I run it using the lines I have given as an example
it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole
number in the line or am I being particularly stupid and missing
something obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it goes
something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end
of the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
M

Muppet

Hi ~Graham

Sorry the text file was more complex that I made it out to be, to me (being
a novice), it looked like a straight forward text file.

I have a small script that removes the following:

=== Rise of the Eldrazi === [Total Buy/Sell Value: 97/114]
=======================================================================================

And all the similar lines, it also changes the [ ] brackets to ( ) and
removes the white spaces after the second bracket and places a = next to it.

I will run your macro and get back to you. I'm away for a few days now so it
will be friday before I can test it fully.


Thank you VERY much for your help in this, you are indeed a MVP!!

Dave


Graham Mayor said:
OK - that was much more complicated than your messages suggested so far :(

Start with a document containing a single column table and put the letters
from the square brackets that you wish to process in the cells of the
table (omitting the square brackets). Save the document and put the
complete path where indicated in the following macro.

The number sequences do not all have the same number of characters and
would throw out the alignment of the text if they were processed as you
originally suggested. The following macro keeps the alignment, but
inevitably this will mean spaces in the number groups. With a document as
large as your sample, it will take a while to run:

Dim oChanges As Document, oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim oPara As Range
Dim rFindText As Range
Dim sFind As String
Dim sLow As String
Dim sHigh As String
Dim i As Long
Dim sFname As String
'**************************************
sFname = "D:\My Documents\Test\Codes.docx"
'**************************************
Set oDoc = ActiveDocument
Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
Set oTable = oChanges.Tables(1)
For i = 1 To oTable.Rows.Count
Set oRng = oDoc.Range
Set rFindText = oTable.Cell(i, 1).Range
rFindText.End = rFindText.End - 1
sFind = "\[" & rFindText & "\]"
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(findText:=sFind, _
MatchWholeWord:=True, _
MatchWildcards:=True, _
Forward:=True, _
Wrap:=wdFindStop) = True
Set oPara = oRng.Paragraphs(1).Range
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + 5
sLow = Trim(oPara.Text)
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + 5
sHigh = Trim(oPara.Text)
If sLow = "" Then
sLow = Format(sHigh - (sHigh * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + Len(sLow)
oPara.Text = sLow
End If
If sHigh = "" Then
sHigh = Format(sLow + (sLow * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + Len(sHigh)
oPara.Text = sLow
End If
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 55
oPara.End = oPara.Start + 6
oPara.Text = ",0,"
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 62
oPara.End = oPara.Start + 2
oPara.Text = ",0"
Loop
End With
Next i
oChanges.Close wdDoNotSaveChanges

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



Graham Mayor said:
It is unpleasantly hot - mid thirties and very high humidity :( I'm off
to the mountains to cool down. When I get back I'll fix the macro to work
with your document


Muppet said:
Glad your enjoying a warm summer's day :)


Thanks for your quick reply. I think if I give you the website address
it would be easier for you to get the information you need.


www.supernovabots.com/prices_0.txt


This is a list used in an online game, please ignore the names to the
right of the list I have a small script that removes them.


Thanks for your help


Dave


Working on the premise that the space is made by a tab character and
having nothing better to do on a very hot Sunday afternoon - the
following might work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value
missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the
second
value is missing) or 25% lower (if the first value is missing) based
upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can do
numbers above 9 and goes to three decimal places, so now it looks
like this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not
others, so when I run it using the lines I have given as an example
it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole
number in the line or am I being particularly stupid and missing
something obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it
goes something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end
of the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 
G

Graham Mayor

It is much easier to develop an application when the end result and what you
are working from are known at the start, instead of drip feeding ever more
information. It seems that this macro is not the end of the story. :(

Run the posted macro on the *original text file* before you strip out the
rest of the unwanted stuff, or it will not work.

The macro will only work on text files that look like the one you posted.

Codes.docx contains a single column table containing. The following. If you
have other similar codes in other documents, you can add them.

10E
5DN
7E
8ED
9ED
ALA
AP
ARB
BOK
CHK
CON
CSP
DD2
DDC
DDD
DDE
DIS
DST
EVE
EVG
EX
FUT
GPT
IN
JUD
LGN
LRW
M10
ME2
ME3
MED
MI
MOR
MRD
OD
ONS
PLC
PRM
PS
RAV
ROE
SCG
SHM
SOK
ST
TD0
TE
TOR
TSB
TSP
UL
UZ
VAN
VI
WL
WWK
ZEN


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Muppet said:
Hi ~Graham

Sorry the text file was more complex that I made it out to be, to me
(being a novice), it looked like a straight forward text file.

I have a small script that removes the following:

=== Rise of the Eldrazi === [Total Buy/Sell Value: 97/114]
=======================================================================================

And all the similar lines, it also changes the [ ] brackets to ( ) and
removes the white spaces after the second bracket and places a = next to
it.

I will run your macro and get back to you. I'm away for a few days now so
it will be friday before I can test it fully.


Thank you VERY much for your help in this, you are indeed a MVP!!

Dave


Graham Mayor said:
OK - that was much more complicated than your messages suggested so far
:(

Start with a document containing a single column table and put the
letters from the square brackets that you wish to process in the cells of
the table (omitting the square brackets). Save the document and put the
complete path where indicated in the following macro.

The number sequences do not all have the same number of characters and
would throw out the alignment of the text if they were processed as you
originally suggested. The following macro keeps the alignment, but
inevitably this will mean spaces in the number groups. With a document as
large as your sample, it will take a while to run:

Dim oChanges As Document, oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim oPara As Range
Dim rFindText As Range
Dim sFind As String
Dim sLow As String
Dim sHigh As String
Dim i As Long
Dim sFname As String
'**************************************
sFname = "D:\My Documents\Test\Codes.docx"
'**************************************
Set oDoc = ActiveDocument
Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
Set oTable = oChanges.Tables(1)
For i = 1 To oTable.Rows.Count
Set oRng = oDoc.Range
Set rFindText = oTable.Cell(i, 1).Range
rFindText.End = rFindText.End - 1
sFind = "\[" & rFindText & "\]"
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(findText:=sFind, _
MatchWholeWord:=True, _
MatchWildcards:=True, _
Forward:=True, _
Wrap:=wdFindStop) = True
Set oPara = oRng.Paragraphs(1).Range
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + 5
sLow = Trim(oPara.Text)
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + 5
sHigh = Trim(oPara.Text)
If sLow = "" Then
sLow = Format(sHigh - (sHigh * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 51
oPara.End = oPara.Start + Len(sLow)
oPara.Text = sLow
End If
If sHigh = "" Then
sHigh = Format(sLow + (sLow * 0.25), "0.00")
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 61
oPara.End = oPara.Start + Len(sHigh)
oPara.Text = sLow
End If
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 55
oPara.End = oPara.Start + 6
oPara.Text = ",0,"
oPara.Start = oRng.Paragraphs(1).Range.Start
oPara.Start = oPara.Start + 62
oPara.End = oPara.Start + 2
oPara.Text = ",0"
Loop
End With
Next i
oChanges.Close wdDoNotSaveChanges

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



Graham Mayor said:
It is unpleasantly hot - mid thirties and very high humidity :( I'm off
to the mountains to cool down. When I get back I'll fix the macro to
work with your document


Glad your enjoying a warm summer's day :)


Thanks for your quick reply. I think if I give you the website address
it would be easier for you to get the information you need.


www.supernovabots.com/prices_0.txt


This is a list used in an online game, please ignore the names to the
right of the list I have a small script that removes them.


Thanks for your help


Dave


Working on the premise that the space is made by a tab character and
having nothing better to do on a very hot Sunday afternoon - the
following might work for you


Dim oPara As Paragraph
Dim oRng As Range
Dim i As Long
Dim iLow As String, iHigh As String
For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.End = oRng.End - 1
Select Case oRng.Words.Count
Case Is = 6
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(6).Text = Chr(9) Then
iLow = oRng.Words(5)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(6).Text = ",0," & iHigh
Set oRng = oRng.Paragraphs(1).Range
oRng.End = oRng.End - 1
oRng.InsertAfter ",0"
End If
Case Is = 7
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 8
If oRng.Words(5).Text = Chr(9) Then
iHigh = oRng.Words(6) & oRng.Words(7) & oRng.Words(8)
iLow = Format(iHigh + (iHigh * 0.25), "0.00")
oRng.Words(5).Text = iLow & ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
iLow = oRng.Words(5) & oRng.Words(6) & oRng.Words(7)
iHigh = Format(iLow - (iLow * 0.25), "0.00")
oRng.Words(8).Text = ",0," & iHigh
oRng.InsertAfter ",0"
End If
Case Is = 9
If oRng.Words(6).Text = Chr(9) Then
oRng.Words(6).Text = ",0,"
oRng.InsertAfter ",0"
End If
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Is = 11
If oRng.Words(8).Text = Chr(9) Then
oRng.Words(8).Text = ",0,"
oRng.InsertAfter ",0"
End If
Case Else
End Select
Next oPara


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Thank you for your help it worked a treat!

One more question...

Some of the lines in the list only have one entry i.e. first value
missing
Text (ROE)= 0.08 or second value missing Text (ROE)=0.05

Is it possible in word to add a value that is 25% higher (if the
second
value is missing) or 25% lower (if the first value is missing) based
upon
the only value on the line?


I suspect this is possible using excel but that I suppose is not a
question
for this group.


Thanks once again for your valuable help.


Dave

Change the search pattern to
(=[0-9.]{2,})[^032^0160^t]{2,}([0-9.]{2,})
i.e. you are looking for at least two digits and not three

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi Graham

Thanks for the prompt reply :)

I have tried the equation below and used the helpful website you
recommended. I have slightly modified the equation so that it can
do numbers above 9 and goes to three decimal places, so now it
looks like this:

(=[0-99.999]{3,})[^032^0160^t]{1,}([0-99.999]{3,})

\1,0,\2,0


And this is a typical section of the text I am trying to change.


Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.06
Text (ROE)=0.04 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.07
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=0.05 0.08
Text (ROE)=8.4 9.2
Text (ROE)=11.5 13
Text (ROE)=19.5 22.5
Text (ROE)=20 23


When I use the replace function it works on some lines but not
others, so when I run it using the lines I have given as an example
it becomes:


Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.06,0
Text (ROE)=0.04,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.07,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0
Text (ROE)=0.05,0,0.08,0

Text (ROE)=8.4,0,9.2,0
Text (ROE)=11.5 13
Text (ROE)=19.5,0,22.5,0
Text (ROE)=20 23



Why has it ignored the two lines? Is it because there is a whole
number in the line or am I being particularly stupid and missing
something obvious in the equation?



Regards



Dave


You should be able to achieve that with the Replace function.
Replace
(=[0-9.]{3,})[^032^0160^t]{2,}([0-9.]{3,})
with
\1,0,\2,0
with the wildcard option set
See http://www.gmayor.com/replace_using_wildcards.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


Hi

Im new to visual basic so any help would be appreciated.

I need to alter the format of some text in a word document, it
goes something like this:


Original Text

text (123)=5.99 9.99

I need it to look like

text (123)=5.99,0,9.99,0

The values shown are not the same on every line


Two questions.

1. How do I insert the ...,0,...,0

2. How do I loop the macro so it repeats on every line to the end
of the document? (The documnet is about 12000 lines long).



Thanks for any help


Dave


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Beta 20
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top