Formula result in VBA in wrong cell

[drluke]

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.

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.

 

[Snakehips]

@drluke
As is, if x = 2 then Range("F2" & x) resolves to. Range("F22")

Edit as below.

For x = 2 To lastRjnl
   On Error Resume Next
   ws2.Range("F" & x).Value = Application.WorksheetFunction.XLookup(ws1.Range("D" & x).Value, rng1, rng2)
   Next x

Hopr that helps.

 

[drluke]

@drluke
As is, if x = 2 then Range("F2" & x) resolves to. Range("F22")

Edit as below.

For x = 2 To lastRjnl
   On Error Resume Next
   ws2.Range("F" & x).Value = Application.WorksheetFunction.XLookup(ws1.Range("D" & x).Value, rng1, rng2)
   Next x

Hopr that helps.

Thank you very much, that helps a great deal.