excel - VBA If a specific cell in Sheet1 contains data > 0, put a specific code on Sheet2 -


i'm given set of data on sheet1 may this:

        b      c      d       e       f       g     h (codes) 31    21957    0     3707     0     1996     356   3422 32       13    0       13     0       13      10   3455 33     4543    0      767     0      413     423   3446 34     6119    0     1033     0      556    1549   3603 35    12238    0     2066     0     1113      56   3703

each row of data corresponds code (see codes column above). example, data in row 31 greater 0 has code 34-22. row 32 34-55, row 33 34-46, row 34 36-03, , row 35 37-03.

how can write macro can have result on sheet2 based on data above:

     ag   ah   ai   aj 57    3    4    2    2  (based on data in cell b31 of sheet1) 58    3    4    5    5  (based on data in cell b32 of sheet1) 59    3    4    4    6  (based on data in cell b33 of sheet1) 60    3    6    0    3  (based on data in cell b34 of sheet1) 61    3    7    0    3  (based on data in cell b35 of sheet1) 62    3    4    2    2  (based on data in cell d31 of sheet1) 63    3    4    5    5  (based on data in cell d32 of sheet1)

and on , forth...

so far, have this:

dim x integer, destlast long, holdval string  if sheets("sheet1").range("b31").value > 0         destlast = sheets("sheet2").range("ag" & rows.count).end(xlup).row + 1         if destlast < 57 destlast = 57         holdval = sheets("sheet1").range("h31").value         x = 1 4             dim columnnumber2 integer             columnnumber2 = 32 + x             sheets("sheet2").cells(destlast, columnnumber2).value = mid(holdval, x, 1)         next x     end if

the problem have write macro every cell (b31:g35). there easier way of doing this?

please let me know if i'm missing information or if have questions i'm asking!

something (not tested -- i'm taking granted body of loop, wrote, want cell b31 , illustrating how modify apply entire range of cells, in column column manner seem want)

dim long, j long j = 2 7 'columns b g     = 31 35         if sheets("sheet1").cells(i,j).value > 0                 destlast = sheets("sheet2").range("ag" & rows.count).end(xlup).row + 1                 if destlast < 57 destlast = 57                 holdval = trim(str(sheets("sheet1").cells(i,8).value))                 x = 1 4                     dim columnnumber2 integer                     columnnumber2 = 32 + x                     sheets("sheet2").cells(destlast, columnnumber2).value = mid(holdval, x, 1)                 next x             end if     next     next j

Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more