I am using xlookup in my macro. The lookup value is in ws1, the lookup range is in ws3 (rng1), the return range is in ws3 (rng2) and the result should be in ws2 in cells F2 and F3. My macro does everything as expected except that the result from the lookup is not shown in F2 & F3. It is much further down in column F in rows 22 & 23. I'm finding the lastrow in ws2 with reference to column E as column F has no data in it.
Any advice appreciated.
VBA Code:
Sub copyJournal()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim lrowD As Long, lastRjnl As Long
Dim rng1 As Range, rng2 As Range
Dim x As Long
Set ws1 = ThisWorkbook.Worksheets("Additions")
Set ws2 = ThisWorkbook.Worksheets("Journal")
Set ws3 = ThisWorkbook.Worksheets("Reference")
lrowD = ws1.Range("D" & Rows.Count).End(xlUp).Row
lastRjnl = ws2.Range("E" & Rows.Count).End(xlUp).Row
Set rng1 = ws3.Range("A13:A24")
Set rng2 = ws3.Range("E13:E24")
For x = 2 To lastRjnl
On Error Resume Next
ws2.Range("F2" & x).Value = Application.WorksheetFunction.XLookup(ws1.Range("D" & x).Value, rng1, rng2)
Next x
End Sub
Any advice appreciated.