Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-07-2014, 03:00 PM
jjreg jjreg is offline Newbie - Need help with VBA code to capitalize last line of addresses Windows 7 64bit Newbie - Need help with VBA code to capitalize last line of addresses Office 2007
Novice
Newbie - Need help with VBA code to capitalize last line of addresses
 
Join Date: Nov 2014
Posts: 2
jjreg is on a distinguished road
Default Newbie - Need help with VBA code to capitalize last line of addresses

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!


Last edited by jjreg; 11-07-2014 at 08:02 PM.
Reply With Quote
  #2  
Old 11-07-2014, 09:14 PM
macropod's Avatar
macropod macropod is offline Newbie - Need help with VBA code to capitalize last line of addresses Windows 7 64bit Newbie - Need help with VBA code to capitalize last line of addresses Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: http://www.vbaexpress.com/forum/show...258#post317258
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-07-2014, 10:19 PM
jjreg jjreg is offline Newbie - Need help with VBA code to capitalize last line of addresses Windows 7 64bit Newbie - Need help with VBA code to capitalize last line of addresses Office 2007
Novice
Newbie - Need help with VBA code to capitalize last line of addresses
 
Join Date: Nov 2014
Posts: 2
jjreg is on a distinguished road
Default

Solution found here

http://www.vbaexpress.com/forum/show...e-of-addresses
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Newbie - Need help with VBA code to capitalize last line of addresses Only Capitalize First Letter & Delete Last Name zulhfreelancer Excel 5 12-10-2012 07:51 AM
Exceptions for 'Capitalize first letter of sentences' gazpacho Word 1 01-12-2012 11:43 AM
Is there a way to capitalize the all the words except the prepositions? Jamal NUMAN Word 2 08-06-2011 11:46 PM
Newbie - Need help with VBA code to capitalize last line of addresses is it possibe to capitalize every letter after a "colon space" angeltread Word VBA 4 01-17-2011 03:50 PM
capitalize first letter of sentences norco1 Word 0 06-25-2006 12:37 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft