Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-08
8
Medium Priority
?
564 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 400 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 200 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 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Assisted Solution

by:nike_golf
nike_golf earned 600 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

671 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