excel - vba copy corresponding values from another workbook? -

i have 2 workbooks:

planner

column k        column ag 123             £100 246             £20 555             £80 

master

column d       column r 123            £100 246            £20 555            £80 

i trying copy values planner, column ag column r (master) item numbers in column d (master) match column k (planner).

my code below produces no error , not producing results - despite being several matches.

please can show me going wrong?

for avoidance of doubt, workbook opening ok finding file.

code:

sub planneropen()  'set variables dim wb2 workbook dim long dim j long dim lastrow long dim app new excel.application      'find planner if len(finddepotmemo)             'if found set planner reference.     app.visible = false 'visible false default, isn't necessary     application.displayalerts = false     application.screenupdating = false     application.enableevents = false     set wb2 = workbooks.open(finddepotmemo, readonly:=true, updatelinks:=false)      'if have our planner lets continue...      'with workbook     wb2.worksheets(1)         lastrow = .cells(.rows.count, "k").end(xlup).row          'lets begin our data merge         j = 2         = 2 lastrow             'if data meets criteria             'check planner turnover             if thisworkbook.worksheets("data").range("d" & j).value = .range("k" & i).value ' check if item number matches                 thisworkbook.worksheets("data").range("r" & j).value = .range("ag" & i).value                  j = j + 1             end if             'continue until results found         next     end      'all done, let's tidy     'close workbooks     'wb2.close savechanges:=false     'app.quit     'set app = nothing      application.displayalerts = true     application.screenupdating = true     application.enableevents = true end if  end sub  function finddepotmemo() string      dim path string     dim findfirstfile string      path = "g:\buying\food specials\2. planning\1. planning\1. planner\" & "8." & " " & year(date) & "\"     findfirstfile = dir$(path & "*.xlsx")     while (findfirstfile <> "")         if instr(findfirstfile, "planner") > 0             finddepotmemo = path & findfirstfile             exit function         end if         findfirstfile = dir     wend  end function 

instead of having 2 for loops, use application.match find matches between values in 2 workbooks.

use code section below replace yours:

    wb2.worksheets(1)         dim matchrow variant '<-- define variable row number if match successful          lastrow = .cells(.rows.count, "k").end(xlup).row          'lets begin our data merge         = 2 lastrow             ' if data meets criteria             ' check planner turnover             ' use application.match find matching results between workbooks             if not iserror(application.match(thisworkbook.worksheets("data").range("d" & i).value, .range("k2:k" & lastorw), 0)) ' check if match successful                 matchrow = application.match(thisworkbook.worksheets("data").range("d" & i).value, .range("k2:k" & lastorw), 0) ' <-- row number match found                 thisworkbook.worksheets("data").range("r" & j).value = .range("ag" & matchrow).value             end if             'continue until results found         next     end