Microsoft Office Forums - View Single Post - Newbie - Need help with VBA code to capitalize last line of addresses

jjreg · 11-07-2014, 03:00 PM

Hi,

I'm new to 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.

Code:

' 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!