excel vba - Replacing a range based on criteria using a macro vba -

-

thank in advance. pretty new vba trying have single column of cells copied 1 column , pasted number based on single criteria, can change. have list in column e , list in column f. want able pull data cells in column e based on adjacent cell in column f. basically, whatever name type "l7", macro pull data column e corresponds name. have far:

private sub worksheet_change (byval target range)   if target.value = "" exit sub     dim rn     rn = 15     if target.row = 7 , target.column = 12       each cel in range("e:e")         if cel.offset(0,1).value = cel.value           range("l" & rn).value = cel.value           rn = rn+1         end if       next cel     end if end sub

now, want do. works if change name in cell "l7". problem not replace data previous time macro ran. if have list of 20 names , 10 names , run macro pull list of 20 names first, won't clear out names list when pull second.

i attempted several different things on this. tried:

sub clear_cells()     sheets("sheet1").range("l15:l100").clearcontents end sub

that didn't return error messages nothing happened.

i tried:

if range("l15:100").value <> ""     range("l15:100").clearcontents end if

that line of code gave me type 13 mismatch error.

simply put, need entire range of cells data being pasted change , there no leftover data previous time macro ran.

your appreciated.

you can call other macro within code:

private sub worksheet_change (byval target range)   if target.value = "" exit sub   call clear_cells()     dim rn     rn = 15     if target.row = 7 , target.column = 12       each cel in range("e:e")         if cel.offset(0,1).value = cel.value           range("l" & rn).value = cel.value           rn = rn+1         end if       next cel     end if end sub

note can done using array formulas, feel better option code not run every time cell changes on worksheet.


Comments

Post a Comment

Popular posts from this blog

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more