Solved

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

Posted on 2008-10-08
8
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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