Newbie still learning - Help with VBA code to capitalize last line of addresses

[jj2396]

Hi,

Still learning VBA and would appreciate some help with some VBA code to capitalize the last line of addresses in a word document.

The addresses can consist of 3, 4 or 5 consecutive lines (usually 3) with 2 lines break between each address as follows:

John Doe
23 Doe St
Doe City, VIC 9999

Jane Doe
Apartment 22
32 Doe Road
Jane City, NSW 3333


How it should look:

John Doe
23 Doe St
DOE CITY, VIC 9999

Jane Doe
Apartment 22
32 Doe Road
JANE CITY, NSW 3333

The code that I put together is from various piece I researched and is as follows, but it capitalizes everything. I can see why it doesn't work as intended but don't know how to resolve this. It capitalizes everything instead of just the last line because it starts capitalizing stating from the first line break it finds instead of the line break just preceding last line of every address. I don't know how to define this as a group (find only the line break just preceding the city & wildcard for the city & comma & the State) and capitalize that.

There may be an easier way to do this, but this is what I came up with.




' Procedure to capitalize city

Dim rTextCityState As Range
Dim vFindTextCityState(7) As String


Dim x As Long


vFindTextCityState(0) = (Chr(13) & "*, VIC")
vFindTextCityState(1) = (Chr(13) & "*, NSW")
vFindTextCityState(2) = (Chr(13) & "*, SA")
vFindTextCityState(3) = (Chr(13) & "*, TAS")
vFindTextCityState(4) = (Chr(13) & "*, WA")
vFindTextCityState(5) = (Chr(13) & "*, NT")
vFindTextCityState(6) = (Chr(13) & "*, QLD")
vFindTextCityState(7) = (Chr(13) & "*, ACT")



For x = 0 To UBound(vFindTextCityState)

With Selection
.HomeKey wdStory
With .Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(FindText:=vFindTextCityState(x), _
MatchWildcards:=True, _
Wrap:=wdFindStop, Forward:=True) = True
Set rTextCityState = Selection.Range 'The found text
With rTextCityState 'Do what you want with the found text
Select Case x
Case Is = 0
rTextCityState.Font.AllCaps = True
Case Is = 1
rTextCityState.Font.AllCaps = True
Case Is = 2
rTextCityState.Font.AllCaps = True
Case Is = 3
rTextCityState.Font.AllCaps = True
Case Is = 4
rTextCityState.Font.AllCaps = True
Case Is = 5
rTextCityState.Font.AllCaps = True
Case Is = 6
rTextCityState.Font.AllCaps = True
Case Is = 7
rTextCityState.Font.AllCaps = True


End Select
End With
Loop 'and look for the next match
End With
End With
Next x



Any help would be much appreciated!

Thanks!

[macropod]

Presumably, these are labels produced by a mailmerge. In that case, you would do better to have the mailmerge do the capitalization for itself. No VBA required.

Cross-posted at: http://www.msofficeforums.com/word-vba/23428-newbie-need-help-vba-code-capitalize-last.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[jj2396]

Hi Paul,

No, the addresses are just copied from 1 document and pasted into a word document and I would like the VBA code to capitalize the last line of every address if possible.

Thanks,

JJ

[macropod]

In that case you could use a macro like:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "[!^13^11]@<[A-Z]{2,3} [0-9]{4}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
i = i + 1
.Text = UCase(.Text)
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
MsgBox i & " records processed."
End Sub

[jj2396]

Hi Paul,

Wow, that works! Not expecting any more, but It would be great if you could shine some light on the .Text= line of code. I get the gist, but it's not all that clear this part

Hope to learn something rather than blindly copy code.

Sorry about the cross-posting! I read the info on the page you provided and it never even entered my mind, Thanks for that tip as well!

Much appreciated

JJ

[macropod]

The expression:
.Text = "[!^13^11]@<[A-Z]{2,3} [0-9]{4}"
is a wildcard Find expression. The first part:
[!^13^11]
tells Word to find something that is not a paragraph break or manual line break. The:
@<[A-Z]{2,3}
tells Word to keep doing that until it find a word consisting of 2 or 3 capital letters. The:
[0-9]{4}
tells Word to find a 4-digit number.

As a whole, therefore, the expression says to find something that is not a paragraph break or manual line break, followed by however many other non-paragraph break & non-manual line break characters there are until a word consisting of 2 or 3 capital letters is found, followed by a space then 4 digits.

For more on the use of wildcards in Word, see:
http://word.mvps.org/FAQs/General/UsingWildcards.htm
http://www.gmayor.com/replace_using_wildcards.htm
A search of forums like this one for 'wildcard' will find many posts demonstrating their use.

[jj2396]

Wow! This is the oposite of the angle I took which was to try to look for the line break preceding the last line of every address. That is really interesting!
Guru is the title you deserve!

Thanks again for all the info, really informative!

Much appreciated,

JJ