Solved

VBScript to find/replace series of keywords in Excel 2003?

Posted on 2008-10-08
8
557 Views
Last Modified: 2012-05-05
Hello,

I have an excel spreadsheet that's essentially a checkout report. Before feeding it into our financial reporting module, I have to basically find/replace all the names of the states/countries, etc, which usually takes a good ~2 hours since we're fairly global.

I'm sure there's probably a way to script this using VBScript or something similar to say

"IF $Cell = 'United States' SET CELL = 'US')
ELSEIF $Cell = ('Australia' SET CELL = 'AU)

Only I've never written a lick of VBScript. I come from a PHP/MySQL background, though I did do some vb 6.0 some years ago, (its been quite awhile) and a bit of C#.  Maybe someone with more experience in this area could point me in the right direction on how to go about accomplishing this task?

Thanks!
-James
0
Comment
Question by:jmoriarty
8 Comments
 
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 100 total points
ID: 22667658
You can do this to do what you are looking for...i have assumed you want to do this replace in sheet1...

Saurabh...
Sub rrr()
Sheets("Sheet1").Select
Cells.Replace What:="United States", Replacement:="US", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Australia", Replacement:="AU", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Open in new window

0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 50 total points
ID: 22667662
jmoriarty,

You don't need VBA for this. I suggest you use a lookup table. There's one in the attached file - adapt it to meet your needs.

1. The yellow area is a range named 'codes'. That range can be on another worksheet - in needs be.
2. The VLOOKUP() in column B uses the range named 'codes'
3. When you've got all the abbreviations, copy and PasteSpecial/Values onto the same column. You can then either delete the full-name column or hide it.

Hope that helps

Patrick
jmoriarty-01.xls
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 200 total points
ID: 22667667
Hi,

The code below will replace these fields on the active worksheet. The pattern for any further combinations should be pretty clear.  The code  looks for an entire cell match to work, so if you have
United States 10
in a cell nothing will happen
but a standalone
United States
will be replaced with US

to use
"              Press Alt + F11 to open the Visual Basic Editor (VBE).
"      From the Menu, choose Insert-Module.
"      Paste the code into the right-hand code window.
"      Press Alt + F11 to close the VBE
"      Go to Tools & Macro &&. Macros and double-click Countries

if you need it to run over all the sheets in a workbook then pls let me know

Cheers

Dave
Sub Countries()
    Dim strArray(1 To 10, 1 To 10), i As Long
 
    strArray(1, 1) = "United States"
    strArray(1, 2) = "US"
    strArray(2, 1) = "Australia"
    strArray(2, 2) = "AU"
 
    Application.ScreenUpdating = False
    For i = 1 To UBound(strArray)
        Cells.Replace strArray(i, 1), strArray(i, 2), xlWhole
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 13

Assisted Solution

by:nike_golf
nike_golf earned 150 total points
ID: 22667772
I would suggest using a case statement which would allow you to add to your list as it grows... also you might check this link since it's your first you will need to understand some basics - http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

***********************************************************

'Alt + F11 to open the Visual Basic Editor
'From the Menu, choose Insert-Module
'Paste the code into the right-hand window
'Press Alt + F11 to close


Public Sub replacestates()
Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("Select range to find/replace.", "Search and replace", Type:=8)
If rng = "" Then Exit Sub

For Each x In rng
Select Case LCase(x)
    Case Is = "united states"
        x.Value = "US"
    Case Is = "australia"
        x.Value = "AU"
    Case Is = "canada"
        x.Value = "CA"
End Select
Next x

End Sub

NG,
0
 

Author Comment

by:jmoriarty
ID: 22676367
Hi Guys,

Thanks very much for all the responses. All of them were very helpful; especially your additional link regarding the basics, nike_golf.

I actually had went ahead and started using brettdj's code snippet, but I ran into interesting issue. When the macro is ran, any blank cells in the sheet, get "BW" entered into them. Is there a reason for that in the code, or did I enter something incorrectly? Attached is a copy/paste of what I'm using.

I also liked the idea of using a lookup table as well - I could see that being extremely useful for a grid based presentation I'm working on. I was a bit hazy on how to actually use the table from a completely separate sheet, but copy/pasting it into the existing temporarily worked okay. I'll need to do some research into that. Thanks for the example, patrickab.

Sub Countries()
    Dim strArray(1 To 10, 1 To 10), i As Long
 
    
    strArray(1, 1) = "Afghanistan"
    strArray(1, 2) = "AF"
    strArray(2, 1) = "Albania"
    strArray(2, 2) = "AL"
    strArray(3, 1) = "Algeria"
    strArray(3, 2) = "DZ"
    strArray(4, 1) = "American Samoa"
    strArray(4, 2) = "AS"
    strArray(5, 1) = "Andorra"
    strArray(5, 2) = "AD"
    strArray(6, 1) = "Arab Emirates"
    strArray(6, 2) = "AE"
    strArray(7, 1) = "Angola"
    strArray(7, 2) = "AO"
    strArray(8, 1) = "Anguilla"
    strArray(8, 2) = "AI"
    strArray(9, 1) = "Antarctica"
    strArray(9, 2) = "AO"
    strArray(10, 1) = "Antigua And Barbuda"
    strArray(10, 2) = "AG"
    strArray(11, 1) = "Argentina"
    strArray(11, 2) = "AR"
    strArray(12, 1) = "Armenia"
    strArray(12, 2) = "AM"
    strArray(13, 1) = "Aruba"
    strArray(13, 2) = "AW"
    strArray(14, 1) = "Australia"
    strArray(14, 2) = "AU"
    strArray(15, 1) = "Austria"
    strArray(15, 2) = "AT"
    strArray(16, 1) = "Azerbaijan"
    strArray(16, 2) = "AZ"
    strArray(17, 1) = "Bahamas"
    strArray(17, 2) = "BS"
    strArray(18, 1) = "Bahrain"
    strArray(18, 2) = "BH"
    strArray(19, 1) = "Bangladesh"
    strArray(19, 2) = "BH"
    strArray(20, 1) = "Barbados"
    strArray(20, 2) = "BB"
    strArray(21, 1) = "Belarus"
    strArray(21, 2) = "BY"
    strArray(22, 1) = "Belgium"
    strArray(22, 2) = "BE"
    strArray(23, 1) = "Belize"
    strArray(23, 2) = "BZ"
    strArray(24, 1) = "Benin"
    strArray(24, 2) = "BJ"
    strArray(25, 1) = "Bermuda"
    strArray(25, 2) = "BM"
    strArray(26, 1) = "Bhutan"
    strArray(26, 2) = "BT"
    strArray(27, 1) = "Bolivia"
    strArray(27, 2) = "BO"
    strArray(28, 1) = "Bosnia And Herzegowina"
    strArray(28, 2) = "BA"
    strArray(29, 1) = "Botswana"
    strArray(30, 2) = "BW"
    strArray(31, 1) = "Bhouvet Island"
    strArray(31, 2) = "BV"
    strArray(32, 1) = "Brazil"
    strArray(32, 2) = "BR"
    strArray(33, 1) = "British Indian Ocean Territory"
    strArray(33, 2) = "IO"
    strArray(34, 1) = "Brunei Darussalam"
    strArray(34, 2) = "BN"
    strArray(35, 1) = "Bulgaria"
    strArray(35, 2) = "BG"
    strArray(36, 1) = "Burkina Faso"
    strArray(36, 2) = "BF"
    strArray(37, 1) = "Burundi"
    strArray(37, 2) = "BI"
    strArray(38, 1) = "Canada"
    strArray(38, 2) = "CA"
    strArray(39, 1) = "Denmark"
    strArray(39, 2) = "DK"
    strArray(40, 1) = "France"
    strArray(40, 2) = "FR"
    strArray(41, 1) = "Germany"
    strArray(41, 2) = "DE"
    strArray(42, 1) = "Ireland"
    strArray(42, 2) = "IE"
    strArray(43, 1) = "Italy"
    strArray(43, 2) = "IT"
    strArray(44, 1) = "Romania"
    strArray(44, 2) = "RO"
    strArray(45, 1) = "Russian Federation"
    strArray(45, 2) = "RU"
    strArray(46, 1) = "Russia"
    strArray(46, 2) = "RU"
    strArray(47, 1) = "Spain"
    strArray(47, 2) = "ES"
    strArray(48, 1) = "Sweden"
    strArray(48, 2) = "SE"
    strArray(49, 1) = "Switzerland"
    strArray(49, 2) = "CH"
    strArray(50, 1) = "Taiwan"
    strArray(50, 2) = "TW"
    strArray(51, 1) = "Trinidad And Tobago"
    strArray(51, 2) = "TT"
    strArray(52, 1) = "Turkey"
    strArray(52, 2) = "TR"
    strArray(53, 1) = "Moldova"
    strArray(53, 2) = "MD"
    strArray(54, 1) = "Malta"
    strArray(54, 2) = "MT"
    strArray(55, 1) = "Republica Dominica"
    strArray(55, 2) = "DO"
    strArray(56, 1) = "Dominican Republic"
    strArray(56, 2) = "DO"
    strArray(57, 1) = "Netherlands"
    strArray(57, 2) = "NL"
    strArray(58, 1) = "Nederlands"
    strArray(58, 2) = "NL"
    strArray(59, 1) = "Norway"
    strArray(59, 2) = "NO"
    strArray(60, 1) = "United Kingdom"
    strArray(60, 2) = "UK"
    strArray(61, 1) = "United States"
    strArray(61, 2) = "US"
    
    
    
 
 
 
    strArray(62, 1) = "Alabama"
    strArray(62, 2) = "AL"
    strArray(63, 1) = "Alaska"
    strArray(63, 2) = "AK"
    strArray(64, 1) = "American Samoa"
    strArray(64, 2) = "AS"
    strArray(65, 1) = "Arizona"
    strArray(65, 2) = "AZ"
    strArray(66, 1) = "Arkansas"
    strArray(66, 2) = "AR"
    strArray(67, 1) = "California"
    strArray(67, 2) = "CA"
    strArray(68, 1) = "Colorado"
    strArray(68, 2) = "CO"
    strArray(69, 1) = "Connecticut"
    strArray(69, 2) = "CT"
    strArray(70, 1) = "Delaware"
    strArray(70, 2) = "DE"
    strArray(71, 1) = "District of Columbia"
    strArray(71, 2) = "DC"
    strArray(72, 1) = "Federated States of Micronesia"
    strArray(72, 2) = "FN"
    strArray(73, 1) = "Florida"
    strArray(73, 2) = "FL"
    strArray(74, 1) = "Georgia"
    strArray(74, 2) = "GA"
    strArray(75, 1) = "Guam"
    strArray(75, 2) = "GU"
    strArray(76, 1) = "Hawaii"
    strArray(76, 2) = "HI"
    strArray(77, 1) = "Idaho"
    strArray(77, 2) = "ID"
    strArray(78, 1) = "Illinois"
    strArray(78, 2) = "IL"
    strArray(79, 1) = "Indiana"
    strArray(79, 2) = "IN"
    strArray(80, 1) = "Iowa"
    strArray(80, 2) = "IO"
    strArray(81, 1) = "Kansas"
    strArray(81, 2) = "KS"
    strArray(82, 1) = "Kentucky"
    strArray(82, 2) = "KY"
    strArray(83, 1) = "Louisiana"
    strArray(83, 2) = "LA"
    strArray(84, 1) = "Maine"
    strArray(84, 2) = "ME"
    strArray(85, 1) = "Marshall Island"
    strArray(85, 2) = "MH"
    strArray(86, 1) = "Maryland"
    strArray(86, 2) = "MD"
    strArray(87, 1) = "Massachusetts"
    strArray(87, 2) = "MA"
    strArray(88, 1) = "Michigan"
    strArray(88, 2) = "MI"
    strArray(89, 1) = "Minnesota"
    strArray(89, 2) = "MN"
    strArray(90, 1) = "Missippi"
    strArray(90, 2) = "MS"
    strArray(91, 1) = "Missouri"
    strArray(91, 2) = "MO"
    strArray(92, 1) = "Montana"
    strArray(92, 2) = "MT"
    strArray(93, 1) = "Maryland"
    strArray(93, 2) = "MD"
    strArray(94, 1) = "Nebraska"
    strArray(94, 2) = "NE"
    strArray(95, 1) = "Nevada"
    strArray(95, 2) = "NV"
    strArray(96, 1) = "New Hampshire"
    strArray(96, 2) = "NH"
    strArray(97, 1) = "New Jersey"
    strArray(97, 2) = "NJ"
    strArray(98, 1) = "New Mexico"
    strArray(98, 2) = "NM"
    strArray(99, 1) = "New York"
    strArray(99, 2) = "NY"
    strArray(100, 1) = "North Carolina"
    strArray(100, 2) = "NC"
    strArray(101, 1) = "North Dakota"
    strArray(101, 2) = "ND"
    strArray(102, 1) = "Northern Mariana Islands"
    strArray(102, 2) = "MP"
    strArray(103, 1) = "Ohio"
    strArray(103, 2) = "OH"
    strArray(104, 1) = "Oklahoma"
    strArray(104, 2) = "OK"
    strArray(105, 1) = "Oregon"
    strArray(105, 2) = "OR"
    strArray(106, 1) = "Palau"
    strArray(106, 2) = "PW"
    strArray(107, 1) = "Pennsylvania"
    strArray(107, 2) = "PA"
    strArray(108, 1) = "Puerto Rico"
    strArray(108, 2) = "PR"
    strArray(109, 1) = "Rhode Island"
    strArray(109, 2) = "RI"
    strArray(110, 1) = "South Carolina"
    strArray(110, 2) = "SC"
    strArray(111, 1) = "South Dakota"
    strArray(111, 2) = "SD"
    strArray(112, 1) = "Tennessee"
    strArray(112, 2) = "TN"
    strArray(113, 1) = "Texas"
    strArray(113, 2) = "TX"
    strArray(114, 1) = "Utah"
    strArray(114, 2) = "UT"
    strArray(115, 1) = "Vermont"
    strArray(115, 2) = "VT"
    strArray(116, 1) = "Virgin Islands"
    strArray(116, 2) = "VI"
    strArray(117, 1) = "Virginia"
    strArray(117, 2) = "VA"
    strArray(118, 1) = "Washington"
    strArray(118, 2) = "WA"
    strArray(119, 1) = "West Virginia"
    strArray(119, 2) = "WV"
    strArray(120, 1) = "Wisconsin"
    strArray(120, 2) = "WI"
    strArray(121, 1) = "Wyoming"
    strArray(121, 2) = "WY"
 
  
 
    Application.ScreenUpdating = False
    For i = 1 To UBound(strArray)
        Cells.Replace strArray(i, 1), strArray(i, 2), xlWhole
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22679107
Hi,

The new variable order broke down at
strArray(29, 1) = "Botswana"
strArray(30, 2) = "BW"

so as
strArray(30, 2)  was blank, any blank cells became Botswana :)
I have adapted your list below (by adding in Botwana twice to fill the gap) , including changing the upper bound of the array to 121

Cheers

Dave
Option Explicit
 
Sub Countries()
    Dim strArray(1 To 121, 1 To 2), i As Long
 
    
    strArray(1, 1) = "Afghanistan"
    strArray(1, 2) = "AF"
    strArray(2, 1) = "Albania"
    strArray(2, 2) = "AL"
    strArray(3, 1) = "Algeria"
    strArray(3, 2) = "DZ"
    strArray(4, 1) = "American Samoa"
    strArray(4, 2) = "AS"
    strArray(5, 1) = "Andorra"
    strArray(5, 2) = "AD"
    strArray(6, 1) = "Arab Emirates"
    strArray(6, 2) = "AE"
    strArray(7, 1) = "Angola"
    strArray(7, 2) = "AO"
    strArray(8, 1) = "Anguilla"
    strArray(8, 2) = "AI"
    strArray(9, 1) = "Antarctica"
    strArray(9, 2) = "AO"
    strArray(10, 1) = "Antigua And Barbuda"
    strArray(10, 2) = "AG"
    strArray(11, 1) = "Argentina"
    strArray(11, 2) = "AR"
    strArray(12, 1) = "Armenia"
    strArray(12, 2) = "AM"
    strArray(13, 1) = "Aruba"
    strArray(13, 2) = "AW"
    strArray(14, 1) = "Australia"
    strArray(14, 2) = "AU"
    strArray(15, 1) = "Austria"
    strArray(15, 2) = "AT"
    strArray(16, 1) = "Azerbaijan"
    strArray(16, 2) = "AZ"
    strArray(17, 1) = "Bahamas"
    strArray(17, 2) = "BS"
    strArray(18, 1) = "Bahrain"
    strArray(18, 2) = "BH"
    strArray(19, 1) = "Bangladesh"
    strArray(19, 2) = "BH"
    strArray(20, 1) = "Barbados"
    strArray(20, 2) = "BB"
    strArray(21, 1) = "Belarus"
    strArray(21, 2) = "BY"
    strArray(22, 1) = "Belgium"
    strArray(22, 2) = "BE"
    strArray(23, 1) = "Belize"
    strArray(23, 2) = "BZ"
    strArray(24, 1) = "Benin"
    strArray(24, 2) = "BJ"
    strArray(25, 1) = "Bermuda"
    strArray(25, 2) = "BM"
    strArray(26, 1) = "Bhutan"
    strArray(26, 2) = "BT"
    strArray(27, 1) = "Bolivia"
    strArray(27, 2) = "BO"
    strArray(28, 1) = "Bosnia And Herzegowina"
    strArray(28, 2) = "BA"
    strArray(29, 1) = "Botswana"
    strArray(29, 2) = "BW"
    strArray(30, 1) = "Botswana"
    strArray(30, 2) = "BW"
    strArray(31, 1) = "Bhouvet Island"
    strArray(31, 2) = "BV"
    strArray(32, 1) = "Brazil"
    strArray(32, 2) = "BR"
    strArray(33, 1) = "British Indian Ocean Territory"
    strArray(33, 2) = "IO"
    strArray(34, 1) = "Brunei Darussalam"
    strArray(34, 2) = "BN"
    strArray(35, 1) = "Bulgaria"
    strArray(35, 2) = "BG"
    strArray(36, 1) = "Burkina Faso"
    strArray(36, 2) = "BF"
    strArray(37, 1) = "Burundi"
    strArray(37, 2) = "BI"
    strArray(38, 1) = "Canada"
    strArray(38, 2) = "CA"
    strArray(39, 1) = "Denmark"
    strArray(39, 2) = "DK"
    strArray(40, 1) = "France"
    strArray(40, 2) = "FR"
    strArray(41, 1) = "Germany"
    strArray(41, 2) = "DE"
    strArray(42, 1) = "Ireland"
    strArray(42, 2) = "IE"
    strArray(43, 1) = "Italy"
    strArray(43, 2) = "IT"
    strArray(44, 1) = "Romania"
    strArray(44, 2) = "RO"
    strArray(45, 1) = "Russian Federation"
    strArray(45, 2) = "RU"
    strArray(46, 1) = "Russia"
    strArray(46, 2) = "RU"
    strArray(47, 1) = "Spain"
    strArray(47, 2) = "ES"
    strArray(48, 1) = "Sweden"
    strArray(48, 2) = "SE"
    strArray(49, 1) = "Switzerland"
    strArray(49, 2) = "CH"
    strArray(50, 1) = "Taiwan"
    strArray(50, 2) = "TW"
    strArray(51, 1) = "Trinidad And Tobago"
    strArray(51, 2) = "TT"
    strArray(52, 1) = "Turkey"
    strArray(52, 2) = "TR"
    strArray(53, 1) = "Moldova"
    strArray(53, 2) = "MD"
    strArray(54, 1) = "Malta"
    strArray(54, 2) = "MT"
    strArray(55, 1) = "Republica Dominica"
    strArray(55, 2) = "DO"
    strArray(56, 1) = "Dominican Republic"
    strArray(56, 2) = "DO"
    strArray(57, 1) = "Netherlands"
    strArray(57, 2) = "NL"
    strArray(58, 1) = "Nederlands"
    strArray(58, 2) = "NL"
    strArray(59, 1) = "Norway"
    strArray(59, 2) = "NO"
    strArray(60, 1) = "United Kingdom"
    strArray(60, 2) = "UK"
    strArray(61, 1) = "United States"
    strArray(61, 2) = "US"
    
    
    
 
 
 
    strArray(62, 1) = "Alabama"
    strArray(62, 2) = "AL"
    strArray(63, 1) = "Alaska"
    strArray(63, 2) = "AK"
    strArray(64, 1) = "American Samoa"
    strArray(64, 2) = "AS"
    strArray(65, 1) = "Arizona"
    strArray(65, 2) = "AZ"
    strArray(66, 1) = "Arkansas"
    strArray(66, 2) = "AR"
    strArray(67, 1) = "California"
    strArray(67, 2) = "CA"
    strArray(68, 1) = "Colorado"
    strArray(68, 2) = "CO"
    strArray(69, 1) = "Connecticut"
    strArray(69, 2) = "CT"
    strArray(70, 1) = "Delaware"
    strArray(70, 2) = "DE"
    strArray(71, 1) = "District of Columbia"
    strArray(71, 2) = "DC"
    strArray(72, 1) = "Federated States of Micronesia"
    strArray(72, 2) = "FN"
    strArray(73, 1) = "Florida"
    strArray(73, 2) = "FL"
    strArray(74, 1) = "Georgia"
    strArray(74, 2) = "GA"
    strArray(75, 1) = "Guam"
    strArray(75, 2) = "GU"
    strArray(76, 1) = "Hawaii"
    strArray(76, 2) = "HI"
    strArray(77, 1) = "Idaho"
    strArray(77, 2) = "ID"
    strArray(78, 1) = "Illinois"
    strArray(78, 2) = "IL"
    strArray(79, 1) = "Indiana"
    strArray(79, 2) = "IN"
    strArray(80, 1) = "Iowa"
    strArray(80, 2) = "IO"
    strArray(81, 1) = "Kansas"
    strArray(81, 2) = "KS"
    strArray(82, 1) = "Kentucky"
    strArray(82, 2) = "KY"
    strArray(83, 1) = "Louisiana"
    strArray(83, 2) = "LA"
    strArray(84, 1) = "Maine"
    strArray(84, 2) = "ME"
    strArray(85, 1) = "Marshall Island"
    strArray(85, 2) = "MH"
    strArray(86, 1) = "Maryland"
    strArray(86, 2) = "MD"
    strArray(87, 1) = "Massachusetts"
    strArray(87, 2) = "MA"
    strArray(88, 1) = "Michigan"
    strArray(88, 2) = "MI"
    strArray(89, 1) = "Minnesota"
    strArray(89, 2) = "MN"
    strArray(90, 1) = "Missippi"
    strArray(90, 2) = "MS"
    strArray(91, 1) = "Missouri"
    strArray(91, 2) = "MO"
    strArray(92, 1) = "Montana"
    strArray(92, 2) = "MT"
    strArray(93, 1) = "Maryland"
    strArray(93, 2) = "MD"
    strArray(94, 1) = "Nebraska"
    strArray(94, 2) = "NE"
    strArray(95, 1) = "Nevada"
    strArray(95, 2) = "NV"
    strArray(96, 1) = "New Hampshire"
    strArray(96, 2) = "NH"
    strArray(97, 1) = "New Jersey"
    strArray(97, 2) = "NJ"
    strArray(98, 1) = "New Mexico"
    strArray(98, 2) = "NM"
    strArray(99, 1) = "New York"
    strArray(99, 2) = "NY"
    strArray(100, 1) = "North Carolina"
    strArray(100, 2) = "NC"
    strArray(101, 1) = "North Dakota"
    strArray(101, 2) = "ND"
    strArray(102, 1) = "Northern Mariana Islands"
    strArray(102, 2) = "MP"
    strArray(103, 1) = "Ohio"
    strArray(103, 2) = "OH"
    strArray(104, 1) = "Oklahoma"
    strArray(104, 2) = "OK"
    strArray(105, 1) = "Oregon"
    strArray(105, 2) = "OR"
    strArray(106, 1) = "Palau"
    strArray(106, 2) = "PW"
    strArray(107, 1) = "Pennsylvania"
    strArray(107, 2) = "PA"
    strArray(108, 1) = "Puerto Rico"
    strArray(108, 2) = "PR"
    strArray(109, 1) = "Rhode Island"
    strArray(109, 2) = "RI"
    strArray(110, 1) = "South Carolina"
    strArray(110, 2) = "SC"
    strArray(111, 1) = "South Dakota"
    strArray(111, 2) = "SD"
    strArray(112, 1) = "Tennessee"
    strArray(112, 2) = "TN"
    strArray(113, 1) = "Texas"
    strArray(113, 2) = "TX"
    strArray(114, 1) = "Utah"
    strArray(114, 2) = "UT"
    strArray(115, 1) = "Vermont"
    strArray(115, 2) = "VT"
    strArray(116, 1) = "Virgin Islands"
    strArray(116, 2) = "VI"
    strArray(117, 1) = "Virginia"
    strArray(117, 2) = "VA"
    strArray(118, 1) = "Washington"
    strArray(118, 2) = "WA"
    strArray(119, 1) = "West Virginia"
    strArray(119, 2) = "WV"
    strArray(120, 1) = "Wisconsin"
    strArray(120, 2) = "WI"
    strArray(121, 1) = "Wyoming"
    strArray(121, 2) = "WY"
 
  
 
    Application.ScreenUpdating = False
    For i = 1 To UBound(strArray)
        Cells.Replace strArray(i, 1), strArray(i, 2), xlWhole
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:jmoriarty
ID: 22682913
oi vey, I should've caught that. Sorry for bothering you with such a simple fix. ;)  I greatly appreciate all the assistance you've provided me with. I first used that script in a production environment this morning, and it was a lovely time saving mechanism.

Thank you all once again, you especially, brettdj.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22682951
No probs :) You certainly put in a lot of work to the setup part, I'll be using this myself one day

Cheers

Dave
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now