i use same block of code @ end of every sub have place various calculations i've made earlier in sub excel sheet in specific locations. each sub represents metric , calculated differently, placement of answers same in each one. wondering if there way create new function or sub separately , call function/sub in each metric sub don't have type code every time. sub cross references title in (k, 4) , (3, g) find place it. each sub starts code , changes depending on metric is:
sub calcmetric_1() dim title1_n, title2_n, title3_n, title4_n long dim cws worksheet set cws = worksheets("sheet 1") title1_n = 0 title2_n = 0 title3_n = 0 title4_n = 0 k = 1 activesheet.cells(rows.count, 4).end(xlup).row if cells(k, 4) = "metric 1"
then code same in each sub:
j = 5 15 cws g = 1 cells(3, columns.count).end(xltoleft).column range(cells(k, j * 4 + 2), cells(k + 1, j * 4 + 4)) on error resume next if cells(3, g) = "title1" cells(k, g).value = title1_n elseif cells(3, g) = "title2" cells(k, g).value = title2_n elseif cells(3, g) = "title3" cells(k, g).value = title3_n elseif cells(3, g) = "title4" cells(k, g).value = title4_n end if end next g end on error goto 0 next j end if next k
Comments
Post a Comment