• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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.
0
Rossamino
Asked:
Rossamino
  • 4
  • 4
1 Solution
 
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
 
RossaminoAuthor Commented:
Is there any way to store the vlookup range data in the VBA code instead of in a separate worksheet?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now