Forum Discussion

Deleted's avatar
Deleted
Jun 16, 2022

VBA and Vlookup question, any advice would be helpful

Hi,  I am new to vba, so this is my scenario: I need to download data from website, then filter out one column (zip code), get rid of non residents: this is what I always do: 1. select all cell f...
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Jun 16, 2022

Deleted 

Sub Test()
    Dim m As Long
    m = Range("A:T").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:T" & m).Replace What:="", Replacement:="Unknown", LookAt:=xlPart
    Range("U2:U" & m).FormulaR1C1 = "=VLOOKUP(RC[-2],'[H:\Zip Codes.xlsx]ZIP'!C1,1,0)"
End Sub
  • Deleted's avatar
    Deleted
    Jun 17, 2022

    HansVogelaar 

    Thanks for your help, I tired your code, but it did not work, and I use macro record again:
    so My logic is:
    in the downloaded excel,

    1. select everything, then replace blank cell as "Unknown"
    2. open the zip code file, then in the downloaded excel column c2, perform Vlookup, to filter out non-local county cases.

     

            Dim m As Long

        m = Range("A:T").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        Range("A1:T" & m).Replace What:="", Replacement:="Unknown", LookAt:=xlPart

        Range("T2").Select

        Workbooks.Open Filename:= _

            "H: \R. County Zip Codes.xlsx"

        Windows("export_3214_061722083025.csv").Activate

        ActiveCell.FormulaR1C1 = _

            "=VLOOKUP(RC[-1],'[R. County Zip Codes.xlsx]ZIP'!C1,1,0)"

        Range("T2").Select

        Selection.AutoFill Destination:=Range("T2:T11101")

        Range("T2:T11101").Select

    End Sub

     

    still give me error notices

Resources