Macro Loop Help

Twizzle008 · #1 09-15-2015, 07:28 AM

Hi

am very new to using macros in word so my knowledge is not the best. I often need to run payslips that come out in a text format. The file can have a range of 5 -100 payslips on it whichare not aligned to one page. I have created a macro (inserted below) which aligns it corretly to one page, which works fine, but in order for it to be repeated i have to either keep repeating the macro until and of data or copy and paste the main data of the macro to suit the number of payslips in the file. Could someone help me loop the macro until there is no more text in the document. I would be very greatful for any assictance.

Sub TestArun()
'
' TestArun Macro
'
Selection.TypeParagraph
Selection.MoveDown Unit:=wdLine, Count:=16
Selection.TypeBackspace
Selection.TypeBackspace
Selection.TypeBackspace
Selection.TypeBackspace
Selection.MoveDown Unit:=wdLine, Count:=17
Selection.TypeParagraph
Selection.TypeParagraph
End Sub

trainingclc · #2 09-15-2015, 11:00 AM

Is there something you could search for that is mentioned only once in each item. If there is you can just do a find and a do while.

Selection.Find

Do While .Execute
"your code"
Loop

Let me know if you need more detail.

Guessed · #3 09-15-2015, 03:13 PM

It would be FAR more efficient to do a find/replace on whatever starts each new page and replace with a style to that which includes a 'page break before' setting.

Twizzle008 · #4 09-16-2015, 09:09 AM

Ah I see what you mean.

The issue i have now then is the employee number appears twice on one payslip. But the Pub name appears at the end of the payslip which is where i would like to implement a page break. The only issue is then the Payslip which starts on the next with the employee number is now halfway down the page.

The payslip starts with an employee number at the top left of the page,

If there was a way of say looking for pub "The Green Man", then putting in a page break, then looking for the next employee number after the pub name ie "1234567" and then pulling the data from the employee number onwards to the top of that page? So that the Next payslip thn starts at the top of the pageHopefully this makes sense.

Many thanks

Guessed · #5 09-16-2015, 03:05 PM

In that case, I would do two search and replaces
Find: The Green Man^p
Replace: ^&^m
Wildcards: OFF

Then
Find: ^m^13{2,}
Replace: ^m^p
Wildcards: ON

macropod · #6 09-16-2015, 05:47 PM

You say the employee # occurs twice on each page. The following macro will insert a page break before the first of each pair of employee #s. If the employee # isn't in the first paragraph for each payslip, a minor code adjustment can be made to accommodate that - you just have to tell us which paragraph on each page it's in. Until that adjustment is made, you'll probably also find the macro deleting the wrong first character in the document, too.

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "<[0-9]{7}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    i = (i + 1) Mod 2
    If i = 1 Then
      .Paragraphs.First.Range.InsertBefore Chr(12)
    End If
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
  .Characters.First.Delete
End With
Application.ScreenUpdating = True
End Sub

Twizzle008 · #7 09-17-2015, 01:14 AM

Hi

Thanks for this, it works except there is now a blank page between each payslip. The first employee number is in the first paragraph.

maybe it can be simplified by just finding the first employee number and bringing it to the top of the page. than looping this. If thats possible?

Thanks alot for your help.

macropod · #8 09-17-2015, 02:19 AM

Quote:

Originally Posted by Twizzle008

Thanks for this, it works except there is now a blank page between each payslip. The first employee number is in the first paragraph.

That would only be possible if:
1. your document already had page breaks between payslips; or
2. you ran the macro twice.

Twizzle008 · #9 09-17-2015, 05:00 AM

Hi

Yes the 2nd payslip is on the next page so by default there would be a page break already

macropod · #10 09-17-2015, 05:11 AM

So, if the page breaks are already there, what are you trying to do?

Twizzle008 · #11 09-17-2015, 05:44 AM

each payslip is not in line with one page , so the further down the document they are further and further out of sync with the page. The first payslip is on one page, but due to the blank lines the the first line of the payslip on the 2nd page (which starts with the employee number) is halfway down the page, so essentially all i want to do is bring the first eployee number of the two that are on each payslip to the top of the page it is on, then the rest should follow suit if that makes sense?

macropod · #12 09-17-2015, 07:23 AM

You'll need to attach a sample document to a post with some representative data (i.e. names/accounts/IDs obfuscated without destroying the format). Four or five payslips should be enough. You can attach a document to a post via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Twizzle008 · #13 09-17-2015, 08:29 AM

Hi Have attached an example, there is a larger than normal hgeader on each page which i would like to leave in for company crest.

Many thanks for your help

macropod · #14 09-18-2015, 03:48 AM

You can do the lot without a macro, using a wildcard Find/Replace, where:
Find = [^13 ]{7,}(<[0-9]{7}>)
Replace = ^p^12 \1

Guessed · #15 09-18-2015, 03:12 PM

Your sample file shows that each payslip has two lines that begin with the employee number and that some have leading spaces. The following macro seems to do the job on that file.

Code:

Sub Cleanup Payslips()
  With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "^p^p^p^p"
    .Replacement.Text = "^p^p"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll 'tighten large spaces a bit
    
    .Text = "^13 [0-9]{7}  "
    .Replacement.Text = "^m^&"
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll 'add page breaks if leading spaced
    
    .Text = "^13[0-9]{7}  "
    .Replacement.Text = "^m^&"
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll 'add page breaks if not leading spaced
  End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to loop through all cells in a table produced by a mail merge	elh52	Word VBA	4	08-31-2014 10:50 PM
Loop Macro to Edit Date	damaniam	Word VBA	7	02-21-2014 07:12 AM
Loop Macro to Edit Date	damaniam	Word VBA	11	12-05-2013 08:28 AM
Macro to loop in Word	Yamaha Rider	Word VBA	2	02-07-2012 05:33 PM
Macro to loop in subfolders, change links, export xml data	Catalin.B	Excel Programming	2	09-08-2011 11:37 PM