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.