excel - VBA: Array & countif -
all,
i struggling on approach take following case in excel vba:
from different sheets created array in vba (4 columns: key, type, date of activity a, date of activity b), e.g.:
i decided not use dictionary, because size of array (the # of cars) given. didn't use simple copy-paste macros + countif. first of all, agree array in vba best approach?
now want have summarized results per activity in table this:
so struggling best approach is: 1) looping on lines in array , paste values 1 one in table 2) looping on cells in table , find corresponding cars 3) copying separate sheet , use countif in table 4) ...
could please in advising? problem clear.
add helper columns in (you can hide these later if wish)
in cell d2
formula =month(c2)
, cell e2
=year(c2)
, same g
, h
on column f
then in result table i've used formula
=countifs($b$2:$b$4,$a8, $d$2:$d$4,month(b$7),$e$2:$e$4,year(b$7))
for activity a, same formula can used activity b (but using columns g
, h
instead of d
, e
results. no need vba
update vba approach
you can try vba approach. you'll need pay attention comments in caps , update input , output. code take input array , assume after column 2 activity date. compile results , write sheet. can work on range of dates it'll auto detect first , last date (populating dates in year) , number of activities. there's lot of loops going on here due flexibility of all, it's being handled in arrays/dictionaries (i.e. in memory) shouldn't performance issue. in fewer should handle in seconds no matter data set size gain on effort isn't worth it.
option explicit public sub generateresults() dim arr variant, tmp variant, dates() double, results object dim long, j long, startrow long, resultsseparator long dim startyear long, endyear long, yr long, mo long dim c ' ******update point @ array****** sheet1 arr = range(.cells(1, 1), .cells(4, 5)).value2 end set results = createobject("scripting.dictionary") j = 3 ubound(arr, 2) if startyear < format(worksheetfunction.min(application.index(arr, 0, j)), "yyyy") startyear = format(worksheetfunction.min(application.index(arr, 0, j)), "yyyy") end if if endyear < format(worksheetfunction.max(application.index(arr, 0, j)), "yyyy") endyear = format(worksheetfunction.max(application.index(arr, 0, j)), "yyyy") end if next j ' 1 12 months in year, 1 2 each activitity. adapated more 12 months redim dates(1 (1 + endyear - startyear) * 12, 1 ubound(arr, 2) - 2) = lbound(arr) ubound(arr) set tmp = nothing ' add dictionary if colour not in array if not results.exists(arr(i, 2)) results.add key:=arr(i, 2), item:=dates ' assign data temporary array can change tmp = results(arr(i, 2)) ' update data activity dates j = lbound(dates, 2) ubound(dates, 2) tmp(12 * (year(arr(i, 2 + j)) - startyear) + month(arr(i, 2 + j)), j) = tmp(12 * (year(arr(i, 2 + j)) - startyear) + month(arr(i, 2 + j)), j) + 1 next j ' write data dictionary results(arr(i, 2)) = tmp next application.screenupdating = false ' ******change want results****** ' starting row of results (change output) startrow = 7 ' how many rows want between activity , b etc. resultsseparator = 3 sheet1 j = lbound(dates, 2) ubound(dates, 2) .cells(startrow + (j - 1) * (resultsseparator + results.count), 1) .value2 = ucase("activity " & split(.cells(1, j).address, "$")(1)) .font.bold = true end next j startrow = startrow + 1 j = lbound(dates, 1) ubound(dates, 1) yr = startyear + iif(j mod 12 = 0, (j / 12) - 1, worksheetfunction.rounddown(j / 12, 0)) mo = iif(j > 12, j - 12 * iif(j mod 12 = 0, (j / 12) - 1, worksheetfunction.rounddown(j / 12, 0)), j) = lbound(dates, 2) ubound(dates, 2) .cells(startrow + (i - 1) * (resultsseparator + results.count), 1 + j) .value2 = dateserial(yr, mo, 1) .numberformat = "mmm-yy" end next next j startrow = startrow + 1 ' loop through dictionary each c in results.keys ' write results activity j = lbound(dates, 2) ubound(dates, 2) .cells(startrow + (j - 1) * (resultsseparator + results.count), 1) .value2 = c range(.offset(0, 1), .offset(0, ubound(results(c), 1))) = application.transpose(application.index(results(c), 0, j)) end next j ' increase row startrow = startrow + 1 next c end application.screenupdating = true end sub
Comments
Post a Comment