Hi All,
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:
Code
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.
Thanks all