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

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