Need VBA to verify state codes and identify outliers

Posted on 2007-03-22
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


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 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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