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

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

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