jmoriarty
asked on
VBScript to find/replace series of keywords in Excel 2003?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
Thank you all once again, you especially, brettdj.
No probs :) You certainly put in a lot of work to the setup part, I'll be using this myself one day
Cheers
Dave
Cheers
Dave
ASKER
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.
Open in new window