Jun 16 2022 09:30 AM - edited Jun 16 2022 09:30 AM
Jun 16 2022 09:30 AM - edited Jun 16 2022 09:30 AM
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
Jun 16 2022 02:56 PM
@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
Jun 17 2022 12:37 PM
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