excel - Subtotal formula with unknown amount of rows in a column when the range is based on a text in another column -
i have subtotal column starts in cell i11 don't know amount of rows change each time. @ end of column multiply functions based on subtotal.
i trying create macro count amount of rows starting @ i11 , go until value in column f equals "value of work done"
this have far:
sub total_amount() 'new_entry macro dim ppc1 worksheet dim rowno integer rowno = activecell.row if ppc1.range("f11:f").value = "value of work done" range("i" & rows.count).end(xlup) .offset(1).formula = "=sum(i11:" & .address(0, 0) & ")" end end if end sub
the answer still comes @ end of column instead of beside "value of work done"
sub total_amount() 'new_entry macro dim ws excel.worksheet dim lrow long dim llast long set ws = worksheets("ppc 1") row = 11 while lrow <= ws.usedrange.rows.count if ws.range("f" & lrow).value = "value of work done" llast = lrow exit end if lrow = lrow + 1 loop ws.range("i" & lrow + 1).formula = application.sum("i11:i" & llast) end sub
dim ws excel.worksheet dim lrow long dim llast long set ws = worksheets("ppc 1") lrow = 11 while lrow <= ws.usedrange.rows.count if ws.range("f" & lrow).value = "value of work done" llast = lrow exit end if lrow = lrow + 1 loop
then write formula whatever column want
ws.range("i" & llast).formula = application.sum("i11:i" & llast)
Comments
Post a Comment