I'm trying to paste down a vlookup, but not across the whole data range. I have some code that has found one year prior to my data range, and then I want to apply the vlookup from there to the lastrow.
My code so far:
Sub quickervlookup() Dim lastrow As Long Dim sh As Worksheet Dim minusdate As Date Dim lastdate As Date Dim startrow As Long Dim Rowfind As Long Dim vRow As Long Dim vRow2 As Long Dim rng As Range ' Set our objects Set sh = ThisWorkbook.Sheets("Upload template - Opened") lastrow = sh.Range("A" & Rows.Count).End(xlUp).row lastdate = sh.Range("L" & Rows.Count).End(xlUp) ' what is the lastdate found in column L minusdate = DateAdd("m", -12, lastdate) ' this will minus 12 months from the lastdate variable above Set rng = sh.Columns("L:L").Cells ' Find the row 12 months prior to last date in the range Rowfind = CLng(minusdate) ' convert the date variable to a long variable vRow = Application.Match(Rowfind, rng, 0) If IsError(vRow) Then 'we cannot find a corresponding date then minus 1 day from the 12 month deduction of the last date Rowfind = CLng(minusdate) - 1 'minus one day vRow = Application.Match(Rowfind, rng, 0) ' find the row again End If startrow = sh.Range("V" & vRow).Select ' the row we found by minusing 12 months sh.Range(ActiveCell & lastrow).Formula = """=IFERROR(VLOOKUP(RC[-17],'Upload template - Settled'!C[-18],1,0), ""Not Converted"")"
The last line is where my error happens
I know I'm close.