Need help on macro for looping

macropod · #16 04-28-2011, 09:00 PM

Hi Kilosub,

I get exactly the same results with Word 2000 and 2007, so I suspect there's an error somewhere in the code you're running in Word 2003. Have you tried replacing that code with a fresh copy from the forum?

kilosub · #17 05-03-2011, 07:39 PM

Hi Macropod,

After replacing the code and rerun again, the result is same as mention earlier. Even I have wider and longer the margin setup. Teoritically it should work across all platform of ms office, strange.

For the time being, I'm using on MS Office 2007 till I've more time to look into in it carefully.

I think we can close this issue, ok?

thanks and cheers!!

kilosub

macropod · #18 05-03-2011, 07:48 PM

Hi Kilosub,

I've marked the thread 'solved'. Do post back, though if you encounter any more issues - or figure out what the Word 2003 issue is.

kilosub · #19 05-03-2011, 08:00 PM

Hi Macropod,

I will. Thanks agian :-)

Cheers!!

kilosub

kilosub · #20 07-22-2011, 08:15 PM

Hi Macropod,

After being low awhile. I'm back to continue on this project. Need your kind help to look into the result output plus the coding which you provided.

Sorry to bug you again!! :-(

Thank you and Cheers!!

kilosub

macropod · #21 07-22-2011, 10:17 PM

Hi Kilosub,

Is this still the problem you had before with the extra line missing before "H/P No:" line?

In addition to not being able to replicate the problem previously with either Word 2000 or Word 2007, I still can't replicate it with either Word 2003 or Word 2010! So, that's four different Word versions it works correctly with. It seems there is something odd about your Word installation.

As a workaround, try adding the following line:

Code:

StrFind = StrFind & "|(Collection date: ___________________)^13{1,}(    H/P No:)"

before the 'StrRep' line and, adding the following expression:
|\1^p^p\2
before the final pair of double quotes on the 'StrRep' line.

kilosub · #22 07-23-2011, 02:40 AM

Thank you for your kind response. I have yet amend the work around which you provided. While I was playing around with the data, I have to agree with you that it's got nothing to do with the software version. I found the error is from the page setup/layout. Different layout giving slight/major changes to the output. Can this be correct?

Btw, how do I un-bold font the following line " Expiry date of rebate voucher :30 June 2011." and bold font the whole line following "Collection date : 1 February 2011 to 31 May 2011."

Thanks heaps, Macropod!! You're my savior.

macropod · #23 07-23-2011, 11:36 PM

Hi Kilosub,

Quote:

Different layout giving slight/major changes to the output. Can this be correct?

Although differences in page layout can affect whether all the lines will fit on a given page (as can font-size and paragraph before/after spacing), it can't have any effect on whether an empty paragraph exists between 'Collection date: ___________________' and ' H/P No:'.

Quote:

how do I un-bold font the following line " Expiry date of rebate voucher :30 June 2011." and bold font the whole line following "Collection date : 1 February 2011 to 31 May 2011."

Try the following version of the code:

Code:

Sub StatementReformatter()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim StrFind As String, StrRep As String, i As Long, x As Long, SBar As Boolean
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
StrFind = "[ ]{8}[0-9]{1,2}/[0-9]{2}/[0-9]{2}(^13[ ]{8}[0-9]{10}^13)|(    BONUS POINTS AS AT)"
StrFind = StrFind & "|(    Your card will expire on[!^13]{1,}^13)|(    Dear valued cardmember[!^13]{1,}^13)"
StrFind = StrFind & "|(^13 Collection date)|(    Expiry date of rebate voucher :)([!^13]{1,}^13)"
StrFind = StrFind & "|(    For enquiries[!^13]{1,}^13)|(    I acknowledged receipt[!^13]{1,}^13)"
StrFind = StrFind & "|(    and / OR[!^13]{1,}^13)|(    Signature:[!^13]{1,}^13)|(    IC/ Passport No:[!^13]{1,}^13)"
StrFind = StrFind & "|(Collection date: ___________________)^13{1,}(    H/P No:)"
StrRep = "^m^p^p^p\1^p|^p^p^p^p\1|^p\1|^p\1^p|^p\1|^p\1#\2^p|^p\1^p|^p\1^p|^p^p\1^p^p|^p\1^p|^p\1^p|\1^p^p\2"
With ActiveDocument
  x = UBound(Split(StrFind, "|"))
  For i = 0 To x
    StatusBar = "Reformatting. Step " & i + 1 & " of " & x + 2
    With .Range.Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchAllWordForms = False
      .MatchSoundsLike = False
      .MatchWildcards = True
      .Text = Split(StrFind, "|")(i)
      .Replacement.Text = Split(StrRep, "|")(i)
      .Execute Replace:=wdReplaceAll
    End With
  Next
  StatusBar = "Reformatting. Step " & i + 1 & " of " & x + 2
  With .Range.Find
    .MatchWildcards = True
    .Text = "(Collection date  :[!^13]{1,})"
    .Replacement.Text = "\1"
    .Replacement.Font.Bold = True
    .Format = True
    .Execute Replace:=wdReplaceAll
  End With
  With .Characters.First
    .Delete
    .Delete
  End With
End With
Application.ScreenUpdating = True
StatusBar = ""
Application.DisplayStatusBar = SBar
End Sub

Note: I've enhanced the code to provide progress feedback via the status bar - this can be helpful when processing takes a while.

kilosub · #24 07-24-2011, 09:04 PM

Hi Macropod,

Sorry for not being able to response promptly to you yesterday as I was down with fever and flu.

Thanks heaps for your enhance code. I find it to complex to understand.. sorry!!

I have run using the new code, everything looks good but with 3 minor issue which need your assistant again.

1. I'm not able to bold this line "Your card will expire on 31/12/11." as original coding it was bold.
2. There's "#" in front the date (eg:Expiry date of rebate voucher :#30 June 2011.)
3. I'm not able to make the "Collection date" line to move to next line. Currently it's combine with the above paragraph.

Thanks again, Macropod!!

Cheers,

Kilosub

macropod · #25 07-24-2011, 10:03 PM

Hi kilosub,

Try the following:

Code:

Sub StatementReformatter()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim StrFind As String, StrRep As String, StrBold As String, i As Long, x As Long, y As Long, SBar As Boolean
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
StrFind = "[ ]{8}[0-9]{1,2}/[0-9]{2}/[0-9]{2}(^13[ ]{8}[0-9]{10}^13)|(    BONUS POINTS AS AT)"
StrFind = StrFind & "|(    Your card will expire on[!^13]{1,}^13)|(    Dear valued cardmember[!^13]{1,}^13)"
StrFind = StrFind & "|(^13    Collection date)|(    Expiry date of rebate voucher :)([!^13]{1,}^13)"
StrFind = StrFind & "|(    For enquiries[!^13]{1,}^13)|(    I acknowledged receipt[!^13]{1,}^13)"
StrFind = StrFind & "|(    and / OR[!^13]{1,}^13)|(    Signature:[!^13]{1,}^13)|(    IC/ Passport No:[!^13]{1,}^13)"
StrFind = StrFind & "|(Collection date: ___________________)^13{1,}(    H/P No:)"
StrRep = "^m^p^p^p\1^p|^p^p^p^p\1|^p\1|^p\1^p|^p\1|^p\1#\2^p|^p\1^p|^p\1^p|^p^p\1^p^p|^p\1^p|^p\1^p|\1^p^p\2"
StrBold = "Your card will expire on[!^13]{1,}|Collection date  :[!^13]{1,}"
x = UBound(Split(StrFind, "|"))
y = UBound(Split(StrBold, "|"))
With ActiveDocument
  For i = 0 To x
    StatusBar = "Reformatting: Step " & i + 1 & " of " & x + y + 2
    With .Range.Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchAllWordForms = False
      .MatchSoundsLike = False
      .MatchWildcards = True
      .Text = Split(StrFind, "|")(i)
      .Replacement.Text = Split(StrRep, "|")(i)
      .Execute Replace:=wdReplaceAll
    End With
  Next
  For i = 0 To y
    StatusBar = "Reformatting: Step " & x + i + 2 & " of " & x + y + 2
    With .Range.Find
      .Replacement.Font.Bold = True
      .Format = True
      .Text = Split(StrBold, "|")(i)
      .Replacement.Text = "^&"
      .Execute Replace:=wdReplaceAll
    End With
  Next
  With .Characters.First
    .Delete
    .Delete
  End With
End With
Application.ScreenUpdating = True
StatusBar = ""
Application.DisplayStatusBar = SBar
End Sub

kilosub · #26 07-24-2011, 10:47 PM

Hi Macropod,

Oops!! the bold didn't work for the following "Your card will expire on 31/12/11." and "Collection date : 1 February 2011 to 31 May 2011.".

The "#" is still appearing. I've highlighted in red in the attachment.

Thanks heaps and much appreciated for your kind assistant till now.

Cheers,

Kilosub

macropod · #27 07-24-2011, 11:37 PM

Curious. Try:

Code:

Sub StatementReformatter()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim StrFind As String, StrRep As String, StrBold As String, i As Long, x As Long, y As Long, SBar As Boolean
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
StrFind = "[ ]{8}[0-9]{1,2}/[0-9]{2}/[0-9]{2}(^13[ ]{8}[0-9]{10}^13)|(    BONUS POINTS AS AT)"
StrFind = StrFind & "|(    Your card will expire on[!^13]{1,}^13)|(    Dear valued cardmember[!^13]{1,}^13)"
StrFind = StrFind & "|(^13    Collection date)|(    Expiry date of rebate voucher :)([!^13]{1,}^13)"
StrFind = StrFind & "|(    For enquiries[!^13]{1,}^13)|(    I acknowledged receipt[!^13]{1,}^13)"
StrFind = StrFind & "|(    and / OR[!^13]{1,}^13)|(    Signature:[!^13]{1,}^13)|(    IC/ Passport No:[!^13]{1,}^13)"
StrFind = StrFind & "|(Collection date: ___________________)^13{1,}(    H/P No:)"
StrRep = "^m^p^p^p\1^p|^p^p^p^p\1|^p\1|^p\1^p|^p\1|^p\1#\2^p|^p\1^p|^p\1^p|^p^p\1^p^p|^p\1^p|^p\1^p|\1^p^p\2"
StrBold = "Your card will expire on[!^13]{1,}|Collection date  :[!^13]{1,}"
x = UBound(Split(StrFind, "|"))
y = UBound(Split(StrBold, "|"))
With ActiveDocument.Range
  For i = 0 To x
    StatusBar = "Reformatting: Step " & i + 1 & " of " & x + y + 2
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchAllWordForms = False
      .MatchSoundsLike = False
      .MatchWildcards = True
      .Text = Split(StrFind, "|")(i)
      .Replacement.Text = Split(StrRep, "|")(i)
      .Execute Replace:=wdReplaceAll
    End With
  Next
  For i = 0 To y
    StatusBar = "Reformatting: Step " & x + i + 2 & " of " & x + y + 2
    With .Find
      .Replacement.Font.Bold = True
      .Format = True
      .Text = Split(StrBold, "|")(i)
      .Replacement.Text = "^&"
      .Execute Replace:=wdReplaceAll
    End With
  Next
  With .Characters.First
    .Delete
    .Delete
  End With
End With
Application.ScreenUpdating = True
StatusBar = ""
Application.DisplayStatusBar = SBar
End Sub

kilosub · #28 07-25-2011, 01:11 AM

Hi Macropod,

Hmm, this "#" thing seems stubborn to go away.. Any chance of getting rid of it? :-)

macropod · #29 07-25-2011, 02:16 AM

Sorry, I thought I'd already fixed that. Try:

Code:

Sub StatementReformatter()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
Dim StrFind As String, StrRep As String, StrBold As String, i As Long, x As Long, y As Long, SBar As Boolean
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
StrFind = "|[ ]{8}[0-9]{1,2}/[0-9]{2}/[0-9]{2}(^13[ ]{8}[0-9]{10}^13)|(    BONUS POINTS AS AT)"
StrFind = StrFind & "|(    Your card will expire on[!^13]{1,}^13)|(    Dear valued cardmember[!^13]{1,}^13)"
StrFind = StrFind & "|(^13    Collection date)|(    Expiry date of rebate voucher :[!^13]{1,}^13)"
StrFind = StrFind & "|(    For enquiries[!^13]{1,}^13)|(    I acknowledged receipt[!^13]{1,}^13)"
StrFind = StrFind & "|(    and / OR[!^13]{1,}^13)|(    Signature:[!^13]{1,}^13)|(    IC/ Passport No:[!^13]{1,}^13)"
StrFind = StrFind & "|(Collection date: ___________________)^13{1,}(    H/P No:)"
StrRep = "|^m^p^p^p\1^p|^p^p^p^p\1|^p\1|^p\1^p|^p\1|^p\1^p|^p\1^p|^p\1^p|^p^p\1^p^p|^p\1^p|^p\1^p|\1^p^p\2"
StrBold = "|Your card will expire on[!^13]{1,}|Collection date  :[!^13]{1,}"
x = UBound(Split(StrFind, "|"))
y = UBound(Split(StrBold, "|"))
With ActiveDocument.Range
  For i = 1 To x
    StatusBar = "Reformatting: Step " & i & " of " & x + y
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchAllWordForms = False
      .MatchSoundsLike = False
      .MatchWildcards = True
      .Text = Split(StrFind, "|")(i)
      .Replacement.Text = Split(StrRep, "|")(i)
      .Execute Replace:=wdReplaceAll
    End With
  Next
  For i = 1 To y
    StatusBar = "Reformatting: Step " & x + i & " of " & x + y
    With .Find
      .Replacement.Font.Bold = True
      .Format = True
      .Text = Split(StrBold, "|")(i)
      .Replacement.Text = "^&"
      .Execute Replace:=wdReplaceAll
    End With
  Next
  With .Characters.First
    .Delete
    .Delete
  End With
End With
Application.ScreenUpdating = True
StatusBar = ""
Application.DisplayStatusBar = SBar
End Sub

kilosub · #30 07-26-2011, 12:34 PM

Hi Macropod,

Receive the data today. If the data is slight different from the dummy data. Will it formatted in correctly?

Thanks and Cheers,

Kilosub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Looping Question	Steve B	PowerPoint	0	01-19-2011 10:44 AM
Looping video	tomasball	PowerPoint	0	10-30-2010 02:38 PM
Unbreakable looping	Dixon	Outlook	0	09-23-2009 02:10 PM
How to Advance from Looping SWF Animation?	BSquared18	PowerPoint	1	07-04-2009 01:56 PM
Looping though Custom Properties in VBA	suekay	Misc	0	05-19-2006 06:10 AM