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.:

enter image description here

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:

enter image description here

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)

enter image description here

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

enter image description here

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

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -