excel - Loop cell offset? -

-

i'm new vba i've done few macros speed processes in workshop automating workshop sheets etc, excuse long winded code, 1 has me stumped.

we have tool sheet our machines , want automate when put 4 digit code in cell i.e "1 4 v" fill out various sections of tool sheets more detailed descriptions parameter worksheet, here code.

sub toolsheet()  'start box 1-----------------------------------------  dim box1 string dim box1array() string   box1 = cells(6, "b").value box1array = split(box1)  'tool description ----------------------------------------  if box1array(0) = 1 worksheets(1).range("c7") = worksheets(4).range("g3") worksheets(1).range("b7") = 1  elseif box1array(0) = 2 worksheets(1).range("c7") = worksheets(4).range("g4") worksheets(1).range("b7") = 2  elseif box1array(0) = 3 worksheets(1).range("c7") = worksheets(4).range("g5") worksheets(1).range("b7") = 3  elseif box1array(0) = 4 worksheets(1).range("c7") = worksheets(4).range("g6") worksheets(1).range("b7") = 4  elseif box1array(0) = 5 worksheets(1).range("c7") = worksheets(4).range("g7") worksheets(1).range("b7") = 5  elseif box1array(0) = 6 worksheets(1).range("c7") = worksheets(4).range("g8") worksheets(1).range("b7") = 6  elseif box1array(0) = 7 worksheets(1).range("c7") = worksheets(4).range("g9") worksheets(1).range("b7") = 7  elseif box1array(0) = 8 worksheets(1).range("c7") = worksheets(4).range("g10") worksheets(1).range("b7") = 8  elseif box1array(0) = 9 worksheets(1).range("c7") = worksheets(4).range("g11") worksheets(1).range("b7") = 9  elseif box1array(0) = 10 worksheets(1).range("c7") = worksheets(4).range("g12") worksheets(1).range("b7") = 10  end if  end sub

i've got 2 problems. 1, if there nothing in cell splits throws error , 2, want repeat process 16 times each time 3 cells down last in worksheet 1 keeping same parameters read in worksheet 4, i've tried looping offset once again if there nothing in cell throws error.

thanks help

iain

edit:

thanks have code running through , works if enter information perfectly.

if len(join(box1array)) > 0  if box1array(1) = 1 range("i5").offset(i, 0) = worksheets(4).range("b3")

although box1array above 0 second part of split not throws error again. tried putting,

if len(join(box1array(1))) > 0  if box1array(1) = 1 range("i5").offset(i, 0) = worksheets(4).range("b3")

but doesn't that.

thanks

iain

1, if there nothing in cell splits throws error

ofcourse throw subscript out of range error since didn't split , hence there no array elements use

you didn't specify delimiter split .....

box1 = cells(6, "b").value box1array = split(box1, "?")    'replace question mark delimiter.      'tool description ----------------------------------------  if box1array(0) = 1

to avoid use check see if array elements exist.

if len(join(box1array)) > 0

2, want repeat process 16 times each time 3 cells down last in worksheet 1 keeping same parameters read in worksheet 4, i've tried looping offset once again if there nothing in cell throws error.

instead of if else use select case box1array(0) structure code.


Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more