Making a macro loop

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beepa - 02 Sep 2006 17:42 GMT

I often need to copy an itemized bill from the Web in order to sort it. I
copy and paste it into Word and it comes in as one huge paragraph. I want to
format it into paragraphs, then convert it to a table using the spaces
between each column to separate into cells. I've written the following macro
to format break each line of data into a separate line (paragraph), but I
need to make it loop. Can anyone tell me where and what code to insert to
make it loop until it reaches the end of my unformatted text (when there is
not another / or : to be found)? Here's what works manually, without a loop:
Sub Phonebill ()
Selection.Find.ClearFormatting
With Selection.Find
.Text = "/"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindAsk
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveLeft Unit:=wdWord, Count:=3
Selection.TypeParagraph
Selection.Find.ClearFormatting
With Selection.Find
.Text = ":"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindStop
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
End Sub

Reply to this Message

Doug Robbins - Word MVP - 02 Sep 2006 17:52 GMT

Easier to tell you the best way to do this if you accurately describe the
formatting of the data that you are starting with.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I often need to copy an itemized bill from the Web in order to sort it. I
> copy and paste it into Word and it comes in as one huge paragraph. I want
[quoted text clipped - 49 lines]
> Selection.MoveRight Unit:=wdCharacter, Count:=1
> End Sub

Reply to this Message

Beepa - 02 Sep 2006 18:07 GMT

Thanks, Doug. Each line of my phone bill has the same number of field, like
line number, date (format is 8/12), time (12:46 PM), city, lenth of call,
charges, etc. Since all of this is run on but each piece of data is
separated by a space, I use the / to locate the cursor, then move to the left
to position the cursor before the line number, then insert a paragraph
symbol, then relocate the cursor past the / to the colon in the time.
Finally, I move the cursor off the colon so the next search will start
properly (going to the next / in the next date). Does this make sense? I'd
like to have this work in a loop. I inserted a "Do" at the beginning and a
"End While Selection.File.Found" at the end, just before "End Sub", but it
created an infinite loop. I think I put those two lines in the wrong place,
but I don't know what I'm doing (I took them from another macro someone
wrote).

Reply to this Message

Doug Robbins - Word MVP - 02 Sep 2006 19:46 GMT

It would be best if I could see the actual data in its original format. Can
you email me a file that contains it.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thanks, Doug. Each line of my phone bill has the same number of field,
> like
[quoted text clipped - 14 lines]
> but I don't know what I'm doing (I took them from another macro someone
> wrote).

Reply to this Message

Beepa - 02 Sep 2006 20:14 GMT

Here's a sample of the Word file I'm trying to format. Please let me know
if you need me to send it differently. Thanks!

1 07/31 9:09AM 847-877-0069 Northbrook, IL 2.0 Included 0.00 0.00 2 07/31
9:12AM 312-546-3247 Chicago, IL 2.0 Included 0.00 0.00 3 07/31 11:44AM
312-546-3247 Chicago, IL 2.0 Included 0.00 0.00 4 07/31 12:02PM
312-255-3777 Chicago, IL 1.0 Included 0.00 0.00 5 07/31 3:56PM 312-255-3777
Chicago, IL 1.0 Included 0.00 0.00 6 07/31 3:57PM 847-448-0490 Evanston, IL
3.0 Included 0.00 0.00 7 07/31 7:01PM 312-255-3777 Chicago, IL 1.0
Included 0.00 0.00 8 07/31 7:03PM 847-877-0069 Northbrook, IL 1.0 Included
0.00 0.00 9 08/01 8:17AM Incoming 15.0 Included 0.00 0.00 10 08/01 11:06AM
Incoming 12.0 Included 0.00 0.00 11 08/01 11:23AM 404-277-9052 Atlanta, GA
11.0 Included 0.00 0.00 12 08/01 11:33AM 404-277-9052 Atlanta, GA 1.0
Included 0.00 0.00 13 08/01 5:08PM 312-255-3777 Chicago, IL 1.0 Included
0.00 0.00 14 08/01 5:11PM 312-546-3247 Chicago, IL 1.0 Included 0.00 0.00
15 08/02 8:39AM 847-840-1080 Northbrook, IL 11.0 Included 0.00 0.00 16
08/02 9:12AM Incoming 3.0 Included 0.00 0.00 17 08/02 12:26PM Incoming
5.0 Included 0.00 0.00 18 08/02 4:11PM 847-840-1080 Northbrook, IL 1.0
Included 0.00 0.00 19 08/03 6:08PM 404-277-9052 Atlanta, GA 13.0 Included
0.00 0.00 20 08/03 6:20PM Incoming 1.0 Included 0.00 0.00 21 08/04 8:11PM
312-255-3777 Chicago, IL 1.0 Included 0.00 0.00 22 08/05 8:36AM Incoming
2.0 Included 0.00 0.00 23 08/05 10:52AM Incoming 2.0 Included 0.00 0.00

Reply to this Message

Beepa - 02 Sep 2006 20:26 GMT

Doug, the Wrap line in the second search routine should be
.Wrap = wdFindContinue
instead of
.Wrap = wdFindStop

I forgot to edit that line before sending you the macro copy (having changed
it to .Wrap = wdFindContinue when I tried putting in the Do command to
make a loop).

Reply to this Message

Doug Robbins - Word MVP - 03 Sep 2006 08:38 GMT

Here is a macro that does what you want, including taking care of the
inconsistencies that were in the data that you sent to me.

Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Execute FindText:="( )([0-9]{1,} [0-9]{2}/[0-9]{2})",
MatchWildcards:=True, Replacewith:="^p\2", Replace:=wdReplaceAll,
Wrap:=wdFindContinue, Forward:=True
.Execute FindText:="([A-Z]{2})^s{1,}", MatchWildcards:=True,
Replacewith:="\1", Replace:=wdReplaceAll, Wrap:=wdFindContinue,
Forward:=True
.Execute FindText:="Incoming^s^s^s", MatchWildcards:=False,
Replacewith:="Incoming^t^t", Replace:=wdReplaceAll, Wrap:=wdFindContinue,
Forward:=True
.Execute FindText:="Incoming^s^s", MatchWildcards:=False,
Replacewith:="Incoming^t^t", Replace:=wdReplaceAll, Wrap:=wdFindContinue,
Forward:=True
.Execute FindText:="^s^s^s", MatchWildcards:=False, Replacewith:="^t^t",
Replace:=wdReplaceAll, Wrap:=wdFindContinue, Forward:=True
.Execute FindText:="^s^s", MatchWildcards:=False, Replacewith:="",
Replace:=wdReplaceAll, Wrap:=wdFindContinue, Forward:=True
.Execute FindText:="^s", MatchWildcards:=False, Replacewith:="",
Replace:=wdReplaceAll, Wrap:=wdFindContinue, Forward:=True
.Execute FindText:=" ", MatchWildcards:=False, Replacewith:="^t",
Replace:=wdReplaceAll, Wrap:=wdFindContinue, Forward:=True
End With
ActiveDocument.Range.ConvertToTable vbTab

Each section of the above code that starts with .Execute and ends with
Forward:=True needs to be on one line in the Visual Basic Editor.

You will note that this is very different from that with which you were
starting. For information on the use of wildcard search and replace as used
in the first couple of them above, see the article "Finding and replacing
characters using wildcards" at:

http://www.word.mvps.org/FAQs/General/UsingWildcards.htm

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Doug, the Wrap line in the second search routine should be
> .Wrap = wdFindContinue
[quoted text clipped - 6 lines]
> to
> make a loop).

Reply to this Message