#1
|
|||
|
|||
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 Last edited by jjreg; 11-07-2014 at 08:02 PM. |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |