Formula result in VBA in wrong cell

drluke

Active Member
Joined
Apr 17, 2014
Messages
311
Office Version
  1. 365
Platform
  1. Windows
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.
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@drluke
As is, if x = 2 then Range("F2" & x) resolves to. Range("F22")

Edit as below.

VBA Code:
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.
 
Solution

drluke

Active Member
Joined
Apr 17, 2014
Messages
311
Office Version
  1. 365
Platform
  1. Windows
@drluke
As is, if x = 2 then Range("F2" & x) resolves to. Range("F22")

Edit as below.

VBA Code:
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.
 

Forum statistics

Threads
1,148,259
Messages
5,745,719
Members
423,969
Latest member
seanguerrero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top