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

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

sql - MySQL query optimization using coalesce -

unity3d - Unity local avoidance in user created world -