VBA and Vlookup question, any advice would be helpful

Occasional Contributor

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 from downloaded data, replace blank cell as unknown

2.open the zip code excel, perform vlookup, then filter out #n/a

 

I tried to apply these steps using record macro: this is what I recorded :

But, what should I edit for the last line? the daily data would not always be the same as 10179 rows, (e.g.,U2:U10179), what if it is 30k rows? or what if it is less than 10k rows? is there an term to use to select the active cells?  

 

Thanks!

 

Range("A1:T1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="", Replacement:="Unknown", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("U2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[H:\Zip Codes.xlsx]ZIP'!C1,1,0)"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U10179")
Range("U2:U10179").Select
End Sub

Capture.PNG

2 Replies

@Tipsy_Jungle 

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

@Hans Vogelaar 

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