Need VBA to verify state codes and identify outliers

Posted on 2007-03-22
Last Modified: 2013-11-25
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.
Question by:Rossamino
  • 4
  • 4

Expert Comment

by:Jignesh Thar
ID: 18774760
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.

Expert Comment

by:Jignesh Thar
ID: 18780344
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.

Author Comment

ID: 18780592
Is there any way to store the vlookup range data in the VBA code instead of in a separate worksheet?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Expert Comment

by:Jignesh Thar
ID: 18780813
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)

Author Comment

ID: 18780874
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

Author Comment

ID: 18781071
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:\ ?

Accepted Solution

Jignesh Thar earned 500 total points
ID: 18781215
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))

Author Comment

ID: 18781616
The complete and final code edited slightly to account for the header row that works like a charm:
    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.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    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, _

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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