U.S State abbreviation to full state name

[rishi.ssh]

Hi all,
First of all i would like to introduce myself i am new this forum and also new to excel macro function and how it works.
I am facing some difficulty while using excel....i have alot of data everyday to work upon....
Dear friends i would like your help if someone could help me out to replace all the U.S states abbreviations in full name....For example AZ to ARIZONA,CO to COLORADO and so on for all short names to full.....My abbreviation is in Column B of a sheet....down by down and many are there...i just want that a macro just replace all the abbreviation and convert them to Full name....Please i am urgently in need of this...and alot of time will be saved of mine....Your help will be highly appreciated.



Secondly is there and way of custom filtering of data in a column so that i just have only the value which is attached with a hyphen i mean "-"
for example it will be like xyz-452,kju-111,RTL-524. More details i will provide that Suppose my data is in column A1 "rgtf | 526435 | puj-624 | 363rgy" column A2 "5171 | TYT-415 | puj-624 | 3686A" and so on similar with in all column A down by down..... I want also an macro or any way so that i have just data in column A that filters my result to just have Values attached with an hyphen(-)..... Like in a1 puj-624....and in a2 TYT-415....and all other is removed...can this happen...i cannot use delimate or width...saperation....Lower and upper case both...
Note my those data is in column A....also There is everytime 3 alphabets than an hyphen(-) and than just 3 numbers....
Please help.
Thankx in advance

 

[Peter_SSs]

..I need the code to autodetect the new Range Created (E.g. F) and based on this new range ... populate the entire State Name into col.Z.

See if these changes help.

Private Sub Worksheet_Change(ByVal Target As Range)
  Static States As String
  Dim c As Range, Changed As Range
  Dim StateCol As Long
  
  StateCol = Range("A1:Y1").Find(What:="State", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
  Set Changed = Intersect(Target, Columns(StateCol), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    If Len(States) = 0 Then
      States = "|AL|Alabama|AK|Alaska|AZ|Arizona|AR|Arkansas|CA|California|CO|Colorado|" & _
              "CT|Connecticut|DE|Delaware|FL|Florida|GA|Georgia|HI|Hawaii|" & _
              "ID|Idaho|IL|Illinois|IN|Indiana|IA|Iowa|KS|Kansas|" & _
              "KY|Kentucky|LA|Louisiana|ME|Maine|MD|Maryland|MA|Massachusetts|" & _
              "MI|Michigan|MS|Mississippi|MO|Missouri|MN|Minnesota|MT|Montana|" & _
              "NE|Nebraska|NV|Nevada|NH|New Hampshire|NJ|New Jersey|NM|New Mexico|" & _
              "NY|New York|NC|North Carolina|ND|North Dakota|OH|Ohio|OK|Oklahoma|" & _
              "OR|Oregon|PA|Pennsylvania|RI|Rhode Island|SC|South Carolina|SD|South Dakota|" & _
              "TN|Tennessee|TX|Texas|UT|Utah|VT|Vermont|VA|Virginia|" & _
              "WA|Washington|WV|West Virginia|WI|Wisconsin|WY|Wyoming|"
    End If
    Application.EnableEvents = False
    For Each c In Changed
      c.Value = UCase(c.Value)
      Range("Z" & c.Row).Value = Split(Split(States & c.Value & "||", "|" & c.Value & "|")(1), "|")(0)
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[IonutC]

Hi Peter,

Thank you for your answer.

I pasted the code into a module and I don;t know why the code is not running. Is saying only the code I already had and this new one is not seeing.
Also I can;t debug the code with F8...I haven't encountered a thing like this until now.

Do you know why?


Thank you,
Ionut

 

[IonutC]

Hi Peter,

I don;t know what it had, but i created anew code line and did a repasted your code.

Now is seeing an error on line

Set Changed = Intersect(Target, Columns(StateCol), Rows("2:" & Rows.Count))

Run-time error '424' Object required


Thank you,
Ionut

 

[Peter_SSs]

I pasted the code into a module ...

Not sure what "module" you are referring to here but this event code must go in a worksheet's module, not a standard module.
Not also that you should only have one Worksheet_Change code in any particular worksheet's module.
For the sheet where you want this code to work, if you right click its name tab and choose view code, the vba window should open at the correct place where this code should be.

I can;t debug the code with F8

For Worksheet_Change code, put a break point (put your cursor in the code line and press F9) on the first line of actual processing code (that is, not a Dim statement). Now make a change in the sheet. If the 'event' fires the code should stop at that break point & you can step through with F8.

 

[IonutC]

Thank you Peter!

Now the code is working, but until the line Set Changed = Intersect(Target, Columns(StateCol), Rows("2:" & Rows.Count)) when I have an error "Run-time error '424' Object required"
What I did wrong?


Kr,
IOnut

 

[Peter_SSs]

Now the code is working, ..

I'm not sure what you mean by that as you are reporting the same error on the same line that you were before.

Please post your full current code so I can double-check - I have not been able to reproduce that particular error in my test workbook.