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

Post a Comment

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

i make custom datepicker directive custom template. but have no idea how start building it... how include date data directive? i appreciate guide or give me advice work on more precisely. it might you! follow below steps html code sample: <label>birth date</label> <input type="text" ng-model="birthdate" date-options="dateoptions" custom-datepicker/> <hr/> <pre>birthdate = {{birthdate}}</pre> <script type="text/ng-template" id="custom-datepicker.html"> <div class="enhanced-datepicker"> <div class="proxied-field-wrap"> <input type="text" ui-date-format="yy-mm-dd" ng-model="ngmodel" ui-date="dateoptions"/> </div> <label> <button class="btn" type="button"><i class="icon-calendar"></i>
Read more

javascript - jQuery date picker - Disable dates after the selection from the first date picker -

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>jquery ui datepicker - default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function () { $("#datepicker1").datepicker(); }); </script> <script> $(function () { $("#datepicker2").datepicker(); }); </script> </head> <body> <p>date 1: <input type="text" id="datepicker1"></p> <p>date 2: <input type="t
Read more