PDA

View Full Version : Solved: Apply code only to column A



av8tordude
04-08-2011, 06:59 AM
Can someone assist in applying this code to only column A. Thanks

Sub Abbreviation2States()
Dim c As Range
For Each c In ActiveSheet.UsedRange
Select Case UCase(c)
Case "AL"
c = "Alabama"
Case "AZ"
c = "Arizona"
Case "AR"
c = "Arkansas"
Case "CA"
c = "California"
Case "CO"
c = "Colorado"
Case "CT"
c = "Connecticut"
Case "DE"
c = "Delaware"
Case "DC"
c = "DISTRICT OF COLUMBIA"
Case "FL"
c = "Florida"
Case "GA"
c = "Georgia"
Case "ID"
c = "Idaho"
Case "IL"
c = "Illinois"
Case "IN"
c = "Indiana"
Case "IA"
c = "Iowa"
Case "KS"
c = "Kansas"
Case "KY"
c = "Kentucky"
Case "LA"
c = "Louisiana"
Case "ME"
c = "Maine"
Case "MD"
c = "Maryland"
Case "MA"
c = "Massachusetts"
Case "MI"
c = "Michigan"
Case "MN"
c = "Minnesota"
Case "MS"
c = "Mississippi"
Case "MO"
c = "Missouri"
Case "MT"
c = "Montana"
Case "NE"
c = "Nebraska"
Case "NV"
c = "Nevada"
Case "NH"
c = "New Hampshire"
Case "NJ"
c = "New Jersey"
Case "NM"
c = "New Mexico"
Case "NY"
c = "New York"
Case "NC"
c = "North Carolina"
Case "ND"
c = "North Dakota"
Case "OH"
c = "Ohio"
Case "OK"
c = "Oklahoma"
Case "OR"
c = "Oregon"
Case "PA"
c = "Pennsylvania"
Case "RI"
c = "Rhode Island"
Case "SC"
c = "South Carolina"
Case "SD"
c = "South Dakota"
Case "TN"
c = "Tennessee"
Case "TX"
c = "Texas"
Case "UT"
c = "Utah"
Case "VT"
c = "Vermont"
Case "VA"
c = "Virginia"
Case "WA"
c = "Washington"
Case "WV"
c = "West Virginia"
Case "WI"
c = "Wisconsin"
Case "WY"
c = "Wyoming"
End Select
Next c
End Sub

av8tordude
04-08-2011, 07:20 AM
I found the answer...

For Each c In ActiveSheet.Range("A:A")

Kenneth Hobs
04-08-2011, 07:26 AM
That is an answer but is it efficient?

Try:
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))

RonMcK
04-09-2011, 09:25 AM
So, what happened to Alaska and Hawaii, don't they count as states?

Aussiebear
04-09-2011, 02:01 PM
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))

Is there a move on to add some more states Ron, that we don't know about?

RonMcK
04-09-2011, 08:11 PM
Is there a move on to add some more states Ron, that we don't know about?

G'day, Aussiebear,

I do hope that you're pulling my leg.

Alaska joined the Union on Jan 3, 1959, and Hawaii followed on Aug 21, 1959.

The citizens of Puerto Rico, in a recent referendum, declined to apply for statehood. Another possible 51st state is our D.C. (District of Columbia, separate from Virginia and Maryland which surround it), analogous to your Australian Capital Territory. And, when I look at wikipedia I see that there are a bunch of other contenders, none of which are likely to become States any time soon.

macropod
04-09-2011, 08:34 PM
Is there a move on to add some more states Ron, that we don't know about?And what do you suppose is in ColumnA? Granted, one wonders why Alaska & Hawaii aren't included (maybe because they're not part of the contiguous States), but for all we know ColumnA has addresses with State abbreviations and the code is to return the State names for those abbreviations (of which there may only be a few or there may be thousands).

Aussiebear
04-10-2011, 01:19 AM
yes Ron, just pulling your chain a wee bit....