excel - VBA vlookup code in different sheet -


i new vba. have 2 sheets under excel: "data", "sheet1".data sheet amended every day. use vlookup vba macro rows added column in sheet1. code doesn't give me results (it works if run macro under same sheet data are). thank you

private sub commandbutton21_click() on error resume next sheet1.range("b3:d500").clear dim dept_row long dim dept_clm long table1 = sheet1.range("a3:a50") table2 = data.range("a3:h24") dept_row = sheet1.range("b3").row dept_clm1 = sheet1.range("b3").column dept_clm2 = sheet1.range("c3").column dept_clm3 = sheet1.range("d3").column each cl in table1   sheet1.cells(dept_row, dept_clm1) = application.worksheetfunction.vlookup(cl, table2, 6, false)   sheet1.cells(dept_row, dept_clm2) = application.worksheetfunction.vlookup(cl, table2, 7, false)   sheet1.cells(dept_row, dept_clm3) = application.worksheetfunction.vlookup(cl, table2, 8, false)       dept_row = dept_row + 1 next cl msgbox "done" end sub 

if going code solution better if dimension worksheets such:

dim sws worksheet   'source worksheet dim tws worksheet   'target worksheet dim acell range  thisworkbook   set sws = .sheets("data")   set tws = .sheets("sheet1") end  each acell in sws.range("a3:a50")   tws      .cells(dept_row, dept_clm1).value = application....      'also check out offset function fetching values in cells left or right of cell looping down through.   end next acell 

alternatively, check out match, index , indirect formulas on how transfer data 1 worksheet another. 3 functions more efficient , accurate vlookup, if transferring 3 cells 1 worksheet another.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -