Need VBA to verify state codes and identify outliers

I need a macro to look at the active cells in column L  and convert the names of the states (and US protectorates) to their respective postal abbreviations and then identify any cells which do not contain a valid state abbreviation by appending a zz to the beginning and doing a sort on the used range of the worksheet.
The code I've got so far selects the range and Replaces the state names with their abbreviations one at a time.  I'm not sure how to check each cell for the valid values of AL, AK, AR etc. and if not valid append the zz.  I'm pretty sure I can figure out how to sort the whole worksheet, but if you know off hand, that'd great.
My current code looks like this:

    Range("l2", "l" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    With Selection
        .Replace What:="Alabama", Replacement:="AL", LookAt:=xlPart
        .Replace What:="Alaska", Replacement:="AK", LookAt:=xlPart
        .Replace What:="American Samoa", Replacement:="AS", LookAt:=xlPart
         { you get the idea}
        .Replace What:="Wisconsin", Replacement:="WI", LookAt:=xlPart
        .Replace What:="Wyoming", Replacement:="WY", LookAt:=xlPart
    End With

I guess the remainder could be done with 50-ish nested if's, but I'm not sure you can nest that many levels and there must be a better way.  Maybe a case statement?  Could there be something more eloquent like building an array or a recordset or something?
Please bear in mind that Excel can have as many as 65,536 rows and that it's not uncommon for me to have 40,000+ entries.
RossaminoAsked:
Who is Participating?
 
Jignesh TharSenior ManagerCommented:
So if you were to keep States.xls excel file having "States" worksheet with state data then formula that would work well is -> Let me know if this works for you

=IF(ISERROR(VLOOKUP(L1,'C:\[States.xls]States'!B$1:B$59,1,0)), IF(ISERROR(VLOOKUP(L1,'C:\[States.xls]States'!A$1:B$59,2,0)), CONCATENATE("zz" & L1), VLOOKUP(L1, 'C:\[States.xls]States'!$A$1:$B$59,2,0)), vlookp(L1, 'C:\[States.xls]States'!B$1:B$59,1,0))
0
 
Jignesh TharSenior ManagerCommented:
The better way would be to
1. Store all the state names like, Alabama, Alaska, etc in A column of separate sheet named "States"
2. If you have your states in L column, put below formula in M1 cell and copy this cell in all adjacent cells as in L column
=IF(ISERROR(VLOOKUP(L1,States!A:A,1,FALSE)),"Not Found","Found")
3. Select "Data -> Filter -> Auto Filter" and select "Not Found" to filter out.
0
 
Jignesh TharSenior ManagerCommented:
If your intention is not to just have "Found" / "Not Found" and if you also want to translate each state to two letter abbreviation, use below
1. Store all the state names like, Alabama, Alaska, etc in A column of separate sheet named "States". Store corresponding abbreviation like AL, AK, etc in column B.
2. If you have your states in L column, put below formula in M1 cell and copy this cell in all adjacent cells as in L column
=IF(ISERROR(VLOOKUP(L1,States!A:B,2,FALSE)),"Not Found",VLOOKUP(L1,States!A:B,2,FALSE))
3. Select "Data -> Filter -> Auto Filter" and select "Not Found" to filter out.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
RossaminoAuthor Commented:
Is there any way to store the vlookup range data in the VBA code instead of in a separate worksheet?
0
 
Jignesh TharSenior ManagerCommented:
Vlookup data should be either stored in same sheet or another sheet. It cannot be read from VBA code. When you have intention to have 40000+ rows, your code will be slower to run if you replace each state.
If having separate sheet is problem you can either choose to have State look up date in same sheet or "Hidden" sheet.  (Sheet can be hidden by menu Format -> Sheet -> Hide)
0
 
RossaminoAuthor Commented:
Here's what I've come up with:

=if(iserror(vlookup(L1,States!B$1:B$59,1,0)), if(iserror(vlookup(L1,States!A$1:B$59,2,0)), concatenate("zz" & L1), vlookup(L1, States!A$1:B$59,2,0)), vlookp(L1, States!B$1:B$59,1,0))

If the cell already contains the postal abbreviation use that, else if the cell contains the name of the state use that, else append zz for later sort.

Hopefully performance will not be an issue
0
 
RossaminoAuthor Commented:
so if the States spreadsheet isn't already open, I can just append the path?  Instead of:
=if(iserror(vlookup(L1,States!B$1:B$59,1,0)), etc . . . I would use
=if(iserror(vlookup(L1,c:\States!B$1:B$59,1,0)), etc . . . if States was in C:\ ?
0
 
RossaminoAuthor Commented:
The complete and final code edited slightly to account for the header row that works like a charm:
    Columns("M").Insert
    Range("M1").Value = "State"
    Range("M2").Value = "=if(iserror(vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0)), if(iserror(vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), concatenate(""zz"" & L2), vlookup(L2,'C:\[States.xls]States'!A$1:B$59,2,0)), vlookup(L2,'C:\[States.xls]States'!B$1:B$59,1,0))"
    Range("M2", "M" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Range("l2", "l" & ActiveSheet.Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row).Select
    Selection.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.