Solved

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

Posted on 2008-10-08
8
554 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 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

12 Experts available now in Live!

Get 1:1 Help Now