making scorecard with data pulled from flat files more dynamic in excel-VBA -


i made sports data none of accurate or remotely right sake of real data using. (sorry it's long explanation) enter image description here

overview: picture of scorecard working on reports on various metrics related sports data. starts scoring of overall category of sports , scoring breakdown of each sport. can call "sports" parent category , ones right child categories.

how data read in: example, games won sub looks through games won column in separate flat file (games won) of data "g t 50 ga" (greater 50 games). looks through category column make sure it's sports category, , goes athlete name find won games. macro writes if athlete, , won more 50 games, add athlete's sport denominator , numerator, else didn't win 50 games add denominator. however, flat files don't contain specific sport each athlete name goes why has outlined in macros tedious , seems unnecessary. heres flat file:

enter image description here

enter image description here

what i'd able do: have created reference file pictured above here has each sport, athlete plays it, , respective hierarchies. somehow, want able have macro goes through flat file, finds criteria needs (ex: g t 50 g, sports) , once finds athlete or hierarchy associated criteria, searches through reference file , associates specific child (sport), , adds sports respective numerators , denominators. if sport or athlete name changes 1 month, scorecard breaks because have go in each individual sub metric , change information. if go reference file , change it, lot easier. again, not entirely sure how go connecting reference file of this.

here's example of sub games won metric:

sub calcmetric_games_won()  k = 1 activesheet.cells(rows.count, 4).end(xlup).row if cells(k, 4) = "games won (percentage)"   dim tennis_n, tennis_d, baseball_n, baseball_d, soccer_n, soccer_d long   dim sports_n, sports_d, finalrow long  dim games_column, name_column, category_column, long   dim cws worksheet    set cws = worksheets("activews")   tennis_n = 0  tennis_d = 0  baseball_n = 0  baseball_d  soccer_n = 0  soccer_d = 0  sports_n = 0  sports_d = 0   thisbook = activeworkbook.name   workbooks.open filename:=thisworkbook.path & "\athleticsdata.xlsb"  sheets("games won").activate  finalrow = cells(rows.count, 2).end(xlup).row  headerrow = cells(finalrow, 2).end(xlup).row      'find metric columns    cells(headerrow, 1).activate    cells.find(what:="games won", after:=activecell, lookin:=xlvalues _    , lookat:=xlpart, searchorder:=xlbycolumns, searchdirection:=xlnext, _    matchcase:=false, searchformat:=false).activate    games_column = activecell.column     cells.find(what:="category", after:=activecell, lookin:=xlvalues _    , lookat:=xlpart, searchorder:=xlbycolumns, searchdirection:=xlnext, _    matchcase:=false, searchformat:=false).activate    category_column = activecell.column     cells.find(what:="athlete name", after:=activecell, lookin:=xlvalues _    , lookat:=xlpart, searchorder:=xlbycolumns, searchdirection:=xlnext, _    matchcase:=false, searchformat:=false).activate    name_column = activecell.column   = (headerrow + 1) finalrow    select case lcase(left(cells(i, name_column).value, 12))   case "williams, serena"         tennis_d = tennis_d + 1         if left(cells(i, games_column).value, 9) = "g t 50 ga"         tennis_n = tennis_n + 1         end if    case "jeter, derek"         baseball_d = baseball_d + 1         if left(cells(i, games_column).value, 9) = "g t 50 ga"         baseball_n = baseball_n + 1         end if     case "beckham, david", "ronaldo, cristiano"         soccer_d = soccer_d + 1         if left(cells(i, aging_column).value, 9) = "g t 50 ga"         soccer_n = soccer_n + 1         end if     end select   next    = (headerrow + 1) finalrow     select case left(cells(i, name_column).value, 4)    case "sports"          sports_d = sports_d + 1         if left(cells(i, games_column).value, 9) = "g t 50 ga"         sports_n = sports_n + 1         end if      end select    next    'write results     workbooks(thisbook).activate     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) = "sports"             cells(k + 1, g).value = sports_n             cells(k + 1, g + 2).value = sports_x         elseif cells(3, g) = "tennis"             cells(k + 1, g).value = tennis_n             cells(k + 1, g + 2).value = tennis_x         elseif cells(3, g) = "baseball"             cells(k + 1, g).value = baseball_n             cells(k + 1, g + 2).value = baseball_x         elseif cells(3, g) = "soccer"             cells(k + 1, g).value = soccer_n             cells(k + 1, g + 2).value = soccer_x                  end if      end    next g    end  on error goto 0 next j    end if   next k  end sub

p.s. ndtr stands no data report


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more