ࡱ> x %7#!"$e'()*+,-./0 234568B:;<=>?@AMDEFGHIJKL1NOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root Entry FFpB//Workbook'_VBA_PROJECT_CUR"-zVBA -, F\psitroy Ba= wbLDUtilities= z<@"1Verdana1Verdana1Verdana1Verdana1Verdana1Verdana1 Verdana1$Verdana1( Trebuchet MS1Verdana1( Trebuchet MS1( Trebuchet MS1( Trebuchet MS"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) 000\-00\-0000                + ) , *       +        !  (  (  +*   *   `     a    @  a#@   @ *  a @ *  !  1(  1  1(  1(  1  1     (      (     +#    `z LDMenuDataJLDPeriod% Release Notes:# NETWORKDAYS EffEndDt2<! EffStartDt2<&LDPeriodColWidth:& LdPeriodLine;' MakeStopperPD;  PctForPrd PctForPrd1:  SalForPrd SalForPrd1: # StartLDPeriod:# StartMenuData:T`=Utility OnAction Proc Procedure In Workbook Menu Item SubMenu Item On Wks Menu On Chart Menu ThisWorkbookRunLDMenuUtility AddPeriod Add &PeriodAdd New Period (F)Add New FOAPAL (F) Set Up EmployeeSet Up Employee (F) SetupEmployeeEffective Period Num Work Days Derived Amt Derived %Amount PercentageFunding SourceNextSOURCE 1SOURCE 2SOURCE 3From/To RemovePeriod&Remove PeriodClearThisPeriodAdd &Fund to Period&Clear this PeriodAddFundLineToPeriodAddStopperPeriod Add Stopper Past DistLoad Extract Data (P)ProcessExtract &Load Extract Forward DistRemove Period from End (F)Clear this Period (F)Add Stopper Period (F)Add New FOAPAL (P)Add Disribution LineAddDistLineToWorkspace Select FundsSelectUserFundsSelect Highlighted Funds About Info AboutInfoAbout LD UtilitiesPrepare PHAREDSPreparePHAREDS Daily Rate:Migrate Workbook (F)MigrateWorkbook&Migrate Workbook(s)B5 y n i< F  6  dMbP?_*+%,"d??U} 1} @1} 1}  1}  1} @1} ` 1} @ 1}  1 ,,,,,,,,@ , , , , 0 0 0 0 0 0 0 0 1 1 1 1 1* 3 11 1+ 1 1 1 111 1, 1 1 1 1"11 1 1 1# 1 1!11 1- 1 1$ 1 1%11 1 1 1 1 111 1: 1 1; 1 1<11 1' 1 1( 1 1& 1)11 11 1 12 1 13 1 1 1. 1 10 1 1/ 1 1 17 1 18 1 17 1 1 24 1 15 1 26 2 1 1pl^^^^^^l^^^>@5 LDMenuData F %  dMbP?_*+%MHP LaserJet 4000 Series PSS odLetterPRIV0''''\KhC]J "d??U} `.} ` }  h@,:@,@,,, h @ , :@,@, ) (  * _1IDAA&/DD  Date Error " B +I3DAA&9$$" B 495 ,      - /!^"HDAA&,DADC $" $B_#IDAA&-DAA&DC" $B - /!^"HDAA&,DADC $" $B_#IDAA&-DAA&DC" $B - /!^"HDAA&,DADC $" $B_#IDAA&-DAA&DC" $B* $~ %?)&PVC%F")'C%F" ) (  * _ 1ID AA&/D D   Date Error " B  +I  3D AA&9$ $ " B  49 5 ,      - /!^"HDAA&,DADC $" $B_#IDAA&-DAA&DC" $B* $~ %?)& C%F")'C%F" ,}F,}F>@5 LDPeriod F &  dMbP?_*+%"D??U}  D>@7 Sheet1 wbLDUtilities ~ LDMenuData*LDUtilMenu &LDPeriod >  !"#$%&'()+,-./0123456789:;<=?@ABCDEFGHIJKLMNPQRSTUVWXYZ[\]^_abcdefghijklmnopqrsuwxyz{|}~8:8#ƙu*E1v#FKV}xME PSLSS<$<<<<N0{00020819-0000-0000-C000-000000000046}X `@i ```  " %`& @`|@ H %%%  h@$  "    (0 8 @ H$h$ $( 0 @ "`hp x]`]x]]]@A@( LDMenuData *%,." StartMenuData .!$%0'A:A "%6 .!2%4'      : "%8!<'& & & ThisWorkbook &$>B@Bk o Workbook_BeforeCloseA@Bo\Attribute VB_Name = "wbLDUtiliti es" Bas0{00020819-0C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDerivBustomLizD2 O@ption 3licit P&* Sub Workbook_Be@foreClF(Cancel As Boolean)Dim i Integer IFRow iL wsa:sheeF!sK6Sng5OXrror ReDsuNex.D elete}MenusA Set @= ThisE5.Fs("LD Dl"3-= AppFaIG .@/("Start CC5 FuncBCountA(ws.Range("A:A")PF@4FiTo B , CA=1.Cells(i, InE3Col)0.ValA If  <> "" XAndA"EeNns().}E@If r A\ iAVA'AOpen(A@= Dis@yOmD <D:>#xME (SLSS<6N0{00020820-0000-0000-C000-000000000046} F@)H` %h%`hh "  ophAttribute VB_Name = "LDMenuData"D Bas0{00020820-C$ 0046} |GlobalSpacFalse dHCrezblPredeclaDIdTru BE xposeTemplateDerivBust`omizaD2 P Sub Worksheet_SelectionChange(ByVal Target As Excel.R ) End G:eR#rFC=Set= Apkaq.Ps("WQ h "). _ B Find'(Type:=msoPopup, Tag:= If Np OIshing ThenI.Delete ?Gd End ځ-(.s7~KSngCosmh, 3/11/2003'above par@ter is necessar^f weeddputCw|`both F} 'eAchar . A@oDt,are@lty tAI ,so@@"sho(uld b@al2l"wi8inHstaatC)"K% 'ssumes 1st@n-titlell in LDANData@@Ca>t1AKppo&pTĩtlCuu,bk6InQ+H=w)AHO@v@\6k& "!x" &Uz1?nue=!'h1no sub0&n:pa ton; else`a :c~b ""(F1' addU>4p'":But,?;; U .bl>*P= 2WBEnabq}/$ $Zz/ Dg'2VIedE <> .qttl:{=@ r_X 5pnuA| 5m1P/01K as /t*cS+}=,R bD- 6 *Dl*B***Z**PYA Next ^!'erq  a*bdd-0{RSaӰφo.0~8VgPus"sf,\pne@s fyHher'0tnu|s xME (SLSS<N0{00020820-0000-0000-C000-000000000046}@(%H0 xpAttribute VB_Name = "LDPeriod"  Bas|0{00020820-; C$0046} |GlobalSpac False dCr@eatablPredeclaDIdTru BE xposeTemplateDv$Bustom izD2 Op tion 0licit (4X    _:#dlgSetupEmployee" 9LDUtilForwardCgSheet1O'LDUtilPastɔ$- : xME@X(@    8  P X (`  H      " @ "  X D`    6 ( 60 h Lp 6    " ( 80 6h  ( P ( bH  & "  "  0@6XL0  b (HNL`L N  Np N N N` N N NX N NHPP^  .0H6`  @ : 0 $ &  @ "` f " $ @  8X  "P &x    f "h $   B ,8 ,h , ,  \^h6hfh T( H ` x     ^H 8`   "  b 8 BH  $6 > B(!p!>x!B!" &" 0"" P" <X"  " " """" 2"  >0#`p# ## #$ F $  fh$ D$ F%,`%&%6%H% b8&6&6&2'H'"P'x' ' ' ' " '' ' B ' d' `( h(Rp((( 2( D) ZX) 4) j)`* dh* * " **+ " 0+ T8+ ++ <++>+ (,H0,x, , ,", ,, -$0->X- --- &- . 0 .(P.:x. H. / /@/`/ / / / /"0 (0"H00p0"0 00 X0P| :xMEPSPSSS<<<<0{CB838312-7782-470B-8EEE-6CF04D4940BF}{7954188B-276F-4928-BD96-90778E18D934} @  `.%  X)x%i% %@  "  "(0 8H "P X :` 4 @` " (H*h    * 8 @ *` "$  (0P " X `p xx A@roX@A@ A@ro0]3Establish source locations for data entered by user Distribution % ldfEmpName76!<' ldfBannerID76!< 000-00-0000$' 'ldfPsNum76!<' DailyRate76!<' DefaultOrg76!<' DefaultAcct76!<'Stopper76!<'PDRate176!<'qo Distribution %C@  ldfEmpName76(<  ldfBannerID76(< 000-00-0000 ldfBannerID76( $ldfPsNum76(< $  DailyRate76(< PDRate176(<0 Scott 11/4C@% Scott 11/4d DailyRate76(<PDRate176(<( Scott 11/4khC@ $ DefaultOrg76(< $ DefaultAcct76(< $Stopper76(<C@q Distribution$B@o  A@ A@rkpoh`Attribute VB_Name = "dlgSetupEmployee"  Bas0{CB838312-7782-470B-8EEE-6CF04D4940BF}{7954188B-276F-4928-BD96-90lE18D934} dGlobalSpacoFalse CreatablPredeclaIdTru BExpoLse0TeateDerivCustomiz D Option 4licit P&* Sub cmdCan cel_Ck() Unl oad MWEn&dOK EnterInfo%UserForm_Initialize" Dim sBID As Sng!'Es]ish sourgloca9s for 2d@e e#Lby u 6Wit h ActSWorkbook.Sheets("DlisL"B tb5ħ.Rang@e("ldf").ValAv B,= A4at(H BannerID , "000-00 CS$PsNum# DailyR}=  efOrgDe`faultW A8cct A Stopperh$PDa1$VA= WnU%_D D.ProtecoNnts:=y5a4F 3K'<FDb}ka2*NrH U:C@(B(cIfl IPc#FJ) Then N?eL=d 'Scott 11/433E¼weE10 0 `IfI.q (5 utH5 q.Pr/#O!+tXon=Calcul0Ko<@kcKeyDowPn(ByX CHodeacMSqXsD.R@rnI9ger, sShi|ftQt `-= vb,-ns sqq` 1 X1 " p1 <x1 X1 2 0282 *@2 *p2422 223 "303"83`3h3 p3Fx33 3 3 .404@4  X4"x4 4 <E5:xME( <6<< <ʘP Bʘ:PBʘx Bʘv Bʐ H ˜ B  H @@ @@ @ @" @ lities v2.0.xls p`::, P%` `fi  0` w (% p` X`ff % % %@ abli`` Sc ` *`((`, :0`'+.` , >!<% @ ` A B ` 33E`D t Da`F e$ HX& % Jx% LPeri% ` ` @@x`D kboo`F `bB@ d@% f`on % h'% n@ ` AAPpds ePr@t es, `v o rePx cel  `  iden%` ` 'us` ion ` 8 0  h the defaP  e$dh  migr Dail ` ` nterPtePDailP'P3P$    HʘBʘ(BʘxBʘzB˜B  @h @ @BLp x(( 0@    XP p@h %hL P6/30/SOURCE 1@@ (5, " 0 H 6h" &      0 @ P `p <2 2 ,@ "p  .HD(:p 0 0 *@H<h> "     (@"Hp,  vhpx&  ." 06@x>44  .5"05.X55 .5  5"5  H6 `6 p66 6 66 "6  7 " 7  787H7X7 `7  Fh77&778808:88 x8L8D8 90989 X9h9P9 9 9 : 4: 4P: : :: :: : ; ,;@;*`;&;;;;B;(<&0< X<h< p<<&<:< =6=6H=== = = " = =&= >& > H>$h>&>&>.>(?.8?(h??*? >? @ 8@X@`@h@ p@ @@ &@ @ "@ @ABAf`AA*ABB.(BXB pBB6B"B"C$(C PC"pC C$C"C"D00D`D DxD ND 2E 0HE vxE 8E$(F 8F$@F `F <hF 6F$JF$D0G$DxG$G$G G HH:H XH2hHH 4H H IIBI`I pI I II II IIIIIIJJ J<0J$pJ J"J J$K"(K"PK&xK K K K"K$K L B(L LpL LL HM XM 6hM M$ M( M, M,N(N(J N(DpN(DN(O$O O (O 0O @O`O O$,O$4O PPP P.(PXP`PxP PP:PPPQ Q0Q 8Q4HQQ QQ QQQ QQQ QRx---------------------------MODULE CONSTANTSExternal - Filenames/ Const wbLD = "ldtool.xls" 'generalize this]ld utilities v2.0.xlah Column constants for LDMenuData]$( %]$]$P ']$(76]$P]$x e7]$x]$ rg7]$6/30/Scottt]  *the offsets from a newly copied eff period76] to the start of next eff period +to set named cell: StartNextEffectivePeriod]SOURCE 1+ used for autofill---------------------------Module GLOBALS]  the currently active LD workbook]9the number of effective periods from name cell, as string]@6the number of effective periods from named cell as int] +the number of named effective peridods that)were added and then later removed by user*because we kept the named values, we do no$need to re-establish the named cells]h]@  Scott 11/4/03]-Scott 11/7/03 'use to identify stopper period]0Scott 11/20/03 'use to identify migration period]8]X  .(ldf$ Scott 11/5/03k use the default value DailyRate$6!<'dh  dont prompt for migration period DailyRate$6!<'d "Please Enter the Period Daily RateSet Daily Rate DailyRate$6!<$'& Scott 11/4/03kk $Period Rate must be numericA@ Scott 11/5/03k('we're in the right workbook, so proceed Distribution$B@ .( NumEffPrds Distribution %%6!<' ' NumTotPrds Distribution %%6!<' No periods exist to delete.A@d'  G'jEffEndDt $6!<'A@A NumEffPrds$6!<'+now set to incremented valueStartNextEffectivePeriod$6.  %(<2enter cardinal number of period A@d 1ProcessNamedEffPrd 'this design not put in placeA@k  EffStartDt $6(<A@ EffPeriod  Distribution %%6%B@Hk  Scott 11/5  .( .( Distribution$B@o AddPeriodp>if both counters are equal, then update both; else just updatethe user counter Distribution %  NumEffPrds$6!< NumEffPrds$6(<  NumTotPrds$6!< NumTotPrds$6(<kdDjust decrement the user counter; no need to ever decrement the total NumEffPrds$6!< NumEffPrds$6(<kqoUpdateEffPrdCounters1move named cell to start of next effective period]'identify the cell with EFFECTIVE PERIOD StartNextEffectivePeriod$6. EffPeriod StartNextEffectivePeriod$65B@~StartNextEffectivePeriod7B@X  StartNextEffectivePeriod   EffPeriod $6%5B@~qHoUpdateStartNextEffectivePeriod0oProcessNamedEffPrdp]"to help identify the NewLine range]ditto]'identify the cell with EFFECTIVE PERIOD]](>used to identify the range to be summed in adding the formulas]@!for the DERIVED amt and pct cells\move the named cell "StartNextEffectivePeriod" to its new location and establish range namesfor new period StartNextEffectivePeriod$6. EffPeriod StartNextEffectivePeriod$65B@~ EffStartDt  EffPeriod $6%5B@~EffEndDt  EffPeriod $6%5B@~WorkDays  EffPeriod $6%5B@~  Scott 11/7/03 SalForPrd  EffPeriod $6%5B@~ FundingEnd  EffPeriod $6%5B@~ PctForPrd  EffPeriod $6%5B@~ DerivedAmt  EffPeriod $6%5B@~ DerivedPct  EffPeriod $6%5B@~dH SalForPrd  EffPeriod $6%5B@~ FundingEnd  EffPeriod $6%5B@~ PctForPrd  EffPeriod $6%5B@~ DerivedAmt  EffPeriod $6%5B@~ DerivedPct  EffPeriod $6%5B@~k FundingStart  EffPeriod $6%5B@~PDRate  EffPeriod $6%5B@~_ Scott 11/4/03(find the NewLineHere range and add name: %. %. NewLineHere   $65B@~:insert date validation into the date start/end date fields3 With Range("EffStartDt" & sNPrds).Validation .Delete .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= ' xlBetween, Formula1:="1/1/2002", Formula2:="1/1/2050" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = ""` .ErrorMessage = "The date is out of bounds or formatted incorrectly. Please correct." .ShowInput = True .ShowError = True End With2 With Range("EffEndDt" & sNPrds).Validation .Delete .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= ' xlBetween, Formula1:="1/1/2002", Formula2:="1/1/2050" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = ""` .ErrorMessage = "The date is out of bounds or formatted incorrectly. Please correct." .ShowInput = True .ShowError = True End With;insert forumulas into the DERIVED AMT and DERIVED PCT cells  Scott 11/5/03 DerivedAmt 5%6!0 ' DerivedAmt 5%6!0 'd` DerivedAmt 5%6!0 ' DerivedAmt 5%6!0 'kV.ActiveSheet.Range("SalForPrd" & sNPrds).Formula = "=WorkDays" & sNPrds & "*DailyRate" =WorkDays *PDRate  SalForPrd 5%6( e Scott 11/4/03 PDRate 5%6(<C Scott 11/4/03 =SalforPrd - sum(D :D ) DerivedAmt 5%6(  =PctForPrd -sum(E :E ) DerivedPct 5%6( Mset range bounds for DERIVED AMT/PCT cells so that we can adjust the formulas  Scott 11/5/03 %. %.  $6B@Hdh %. %.  $6B@HkXreplace formula from utilities workbook to one that adds the appropriate cardinal number   $6 !  SalForPrd SalForPrd $" (  qPoProcessNewEffPrdX[copies the period template from the utilities workbook to the named cell in the LD templateworkbook; Application.EnableEvents = False 'remove after testing$is set in calling proc )Scott 11/7/03 to filter if Stopper Period MakeStopperPDLDPeriod $>%%6!$B@&StartNextEffectivePeriod Distribution %%6B@(d StartLDPeriodLDPeriod $>%%6!$B@&StartNextEffectivePeriod Distribution %%6B@(kApplication.EnableEvents = TrueoCopyPeriodTemplate]-cardinal number of effective period sequence"  .( .(ldf$ Distribution$B@ .+find the right place to insert the new line7first, find the cardinal number of the EFFECTIVE PERIOD  b1 0!$%6!< b1 0!$%6!< NumEffPrds$6!<b1 0!$%6!<',db2 0!$%6!<',k@NOTE: Behavior re CURRENT REGION in Excel XP different from OSX`now find the insert row, insert shifting cells down, copy the previous last row up to the insert>this preserves the formulas in the last row; keeps NewLineHere@and keeps the range for the SUM function in the worksheet intact 4 NewLineHere , %6B@2 NewLineHere , %6B@& NewLineHere , %6%B@( NEW SOURCE NewLineHere , %6%8(<[after copy, we want to make sure to clear the amt/pct cells in the new row and unlock cellsb1:c1 NewLineHere , %6%6B@6b1:c1 NewLineHere , %6%6(8A1 NewLineHere , %6%6B@Hk Distribution$B@k  .( .(oAddFundLineToPeriodldf$G B@<jo0]if here, then we have the right worksheet; now test if we are in an "effective period" region]p'. 0!8B user is in a locked cell; so will say they are not in right place'.d(, Set rngTestRange = ActiveCell.CurrentRegion> If rngTestRange.Range("a1").Value <> "Effective Period" ThenT InAnEffectivePeriod = False 'cell isn't locked but still not in right structure4.at the moment employee cells are not locked...cEnd If 'this could change, making this 2nd test unnecessaryk .GDAction not allowed. Active cell must be in EFFECTIVE PERIOD region.A@ji InAnEffectivePeriodEnterFund stubA@o EnterFundMDelete the last effective period on "Distribution" sheet; update control info]](]@-start/end of effective period to remove/clear]`]0move to here named cell StartNextEffectivePeriod'DOK to remove last period?N R T V P$'F F XG'Dj Dldf$ .( Distribution$B@ find range NumEffPrds$6!<' EffPeriod $6.L EffPeriod $6!Z.H?StartNextEffectivePeriod$6%!Z.Jclear contents of rows H J$6B@\ H J$6B@^decrement the eff period numberA)reset named cell StartNextEffectivePeriodStartNextEffectivePeriod$B@X StartNextEffectivePeriod L B@~Aremove existing named cells associated with this effective period EffPeriod $B@X EffStartDt $B@XEffEndDt $B@XWorkDays $B@X SalForPrd $B@X PctForPrd $B@X DerivedAmt $B@X DerivedPct $B@X FundingStart $B@X FundingEnd $B@X NewLineHere $B@XPDRate $B@X( Scott 11/5/03 Distribution$B@ .( Distribution$B@Rhow about updating the "calculator" sheet? -- does the calc statement above do it?ko RemovePeriod6clear the user values of the selected effective periodQreplace FUND with SOURCE autofill values (in order to ensure CurrentRegion works)reinsert the start date]]] to reinsert the start date](current region of selection]H&start/end of effective period to clear]hldf$| k  Distribution$B@ . Distribution$B@| k 'DOK to clear this period?N R T V P$'F F XG'Dj Dldf$ .((find cardinal number of effective period 0!$.db1 d%6!<'grab the start date EffStartDt $6!<'bclear effective dates EffStartDt $6B@6EffEndDt $6B@6'clear funding sources and amt/ct values FundingStart $6.f FundingEnd $6%.h f h$6B@6' Range(rngStart, rngEnd).Locked = FalseReinsert the start date b EffStartDt $6(<Aautofill the funding sources so that CurrentRegion remains viable  f(< FundingEnd $6.h f h$6l fB@j .( B@k(  Distribution$B@o ClearThisPeriod Scott 11/5/03]]]('@loop through periods <> 100% and add stopper line to each period Distribution$Stopper76!<$z SUSPENSE'tdStopper76!<'tk Calculator %-add Calculator Tab LineC@ 4FundCol76!$!|!~FundCol76%!ZB@2 tFundCol76!$!|!~FundCol76%(<C@q NumEffPrds76!<'pC@ r p DerivedPct r76!< DerivedPct r76!<  FundingEnd r76B@ FundingEnd r76(8A@*Stopper76!< FundingEnd r76(< DerivedPct r76!< 0%(<C@ FundingEnd r76(8:relock modified cell B@k rC@&now if applicable add a stopper period NumEffPrds76!<'p Z$6/30/ Z$$ yyyy Z$$'vd@  Z$'vk EffEndDt p76$ v$ Itime gap existsA@Stopper76!<$z SUSPENSE'tdStopper76!<'tkh vEffEndDT p 76(<Stopper76!< FundingEnd p 76(<C@Stopper Record EffPeriod p 76(< FundingEnd p 76%(<kxqp'o`@Scott 11/12/03] FileDialog]old file name full path]old workbook name]new file name full path]new workbook name] worksheet header]8 effective period header]p effective period funding]  calculator tab funding source(s)] for workbook object holder]  flag to test if workbook is open] ( @  loop counters]X  ttl fs rows]p number of effective periods7Create a FileDialog object as a File Picker dialog box.  .%.'not a stopper period'''ldf$ !' Select the file(s) to migrate95B@\ Workbooks*.xls5B@~5< 55!~5,let user name the file if just one migrationEnter New File Name for  New File Name   $_V2.xls$'d0>   $_V2.xls'k= $z Gj  1do not overwite the old file  >B@ !' Distribution % NumEffPrds76!<'Epull ws header info NumEffPrds76!<+ DefaultOrg76!<+ DefaultAcct76!<+Stopper76!<+ DailyRate76!<+ldfPSNum76!<+ ldfBannerID76!<+ ldfEmpName76!<+ NumTotPrds76!<+   A pull pd nfo   EffPeriod 76!<  +W period text EffPeriod 76%!<  +dperiod # EffStartDt 76!<  +EffEndDt 76!<  + FundingEnd 76!0 FundingStart 76!0   +E # offset rows  $Aadjust where only 1 fs  'd9   $ 'k9    @ [account for base 0   $Gpull fund info  FundingStart 76%!< +ofs name  FundingStart 76%!< +o$  FundingStart 76%!< +o%  +?pd #  '  q7 Calculator %Aget the calculator fundsC@FundCol76!$!|!~     FundCol76%!< + C@q6  >4.for multi-select must reopen workbook template !  $>B@'yp6dh6'kX6    >B@ $>B@k6 Distribution %C@'C@$ DefaultOrg76(<Epush ws header info$ DefaultAcct76(<$Stopper76(<$ DailyRate76(<$ldfPSNum76(<$ ldfBannerID76(<$ ldfEmpName76(<$ NumTotPrds76(<  7 build periodsA@ C@$ NumEffPrds76(<  7 push pd infoC@  $ EffPeriod 76(<Ypd text  $ EffPeriod 76%(<fpd #  $$ EffStartDt 76(<e pd start dt  $$EffEndDt 76(<c pd end dtC@$PDRate 76(<K default rate   $ Epd #  FundingEnd 76B@A@*k1 $  FundingStart 76%(<sfs name $  FundingStart 76%(<s$ $  FundingStart 76%(<s%  'k0 ' load the calculator funds Calculator %    FundCol76%B@6 $  FundCol76%(< q/q/d/Cannot use same file name for A@k/  B@ $>B@B 5!~ Workbook  was successfully migratedA@d/  > ! *! B@Bk. 5!~ Workbook(s) have been MigratedA@kx.dThe user pressed Cancel.P.kH.q@.k8.0..'oMigrate Workbook.Attribute VB_Name = "LDUtilForward" Option Explicit '-:MODULE CONSTANTSdExternal - Filens ' Const wbLDldtool.xls"'generalize thi2 04 uities v2.0?aColumn cdants for LDMen uDataNPubmC +ity#.1 On,Ac2Procedure3InWorkbook4FPItem5Sub 6OnW,ks#7Chrt8gcvFYZ6/30/" 'Scot CpNextEffPrdRow@Offset 9Aqthe oB_rom a newlyepied eff period,0oe start@@xtJ 0'to 'd cell: SAD0evveP#]E,cFirstSource As!@ringALSO@URCE 1' us;aut`ofill\odule GLOBALDim XsWBG'AYc@urrentVaB1 w *N@hsH( number)1# D2D6%%, as/ # i& Inte>g? @/! inIiNTote4 C+dods Cay6'were add0and!aIn la removby4r 'beca9 we kep t values,!do n2o ne``Rre-eZsh T#A#Cou ,rsXEqu@@?B`ean0vPDDa0ilyR;Va ri@Du 11/4/0 ! bStpopPDd7 'aidIify@>optuaMigr 5 20R mʥ~ Addo()!A rngCv Range#dtLastEnd@oApaa.Calcu== xlMan*>If Not InTheRight("ldf") @.eCGo!exit_A_subh)5 ? Ifc@ %0 -defauxlt BHc J;= B("=").VaNEls"A 4 dont p`p`+Hq|ge( /putBox("Please Ea-2/c pte", "Sd_)/1()) <= 0LMsg L " "7mupbe Tcc/"//"] J'Jin r(\, so QcCSheetsis.!-t ]Atu4L>*Dtp" & #z?!Copy3)Tempamall UpdZSE(1 Q? 'nowanc`s#0lLKQ;[[ C e.3(0, 1%e=D 'r ci1Ʒe1?!ssNewCoM'Pamed is designcVut <c8rЯQ19I?0? k#+ ˱1coc7=}E)62 )15S3c% r:r_{|IZ{Amc_L5JJpR# Q =}'&a4rs(Dir` 6if bothX a]eќb,^n u9B; eF jQec1.1Wi00 = 1 Then Range("NumEffPrds").Valuel+ 1|If CountersE0qual Tot ]End If Else 'just decrement the user c; no need to ev +tottal9-zIMW`ith OSub 'Upda te Pu`blic StartNextectivePeriod() 'move namccellf"s ofnxt eff p@Dim rng#<As  'idify)w- EFFECTIVE PER8IODl5 AB%WorkbooFkMSet=("5"5.N5s.Addb A:="A,B1" & sN, F_A RefT8o:=%.DeleteO' (6) 6.Off0set(?Row, Col?DR, Process!0drOXewODP"#OFP ' rhelp jQNew@Line rA 2 eXnd@'dittobC]th]$ # iow `Ig>'brbe sumtin @adding"lformulas$ g5: '@BDoVED amt a`pct t`RCȂmitsw lo`catio e sh#.@ AlBnewF?*??)_D"<\Dt Y)et1, 1`=? C ) 2: Days7(& 2k{bS topPDR{ 'Scott 11/7/03'q 1QSalFor0KZ/s5oebFun5odWv4, 0qPct ' oaD%vedAm(s b`3 ktP os4x1?!e|?!s, O[o6p7?!'?!d(d6?!?!oo0' ?!=?! ' p?!rd?!ەz:!Pe...W(((( <PDRppQ4)O4O/'fiQxtHer{ u:'Բ3=۳e(t0O_ Xo߈ ' , 4s?inPא  0vali0!inElsabAd?7?e^` IfG''(Salnp`."=WorkDays"*DailyR? Dq! *PD FD4DuazvPD&_ ?M  )*)!%- sum(D'#F 'G& ":"6i$@)+$_Pc*_ @E"AJset ra /P sat w`an adjus~t`B F) 2SAngB= 8rng@.Off(4, 3>)g !dA/ll4CHpw,bDnd).SPtctC  ?et(6, ! E Eb'replacePa from utilities wDbook to one adcapp0yriD cardinal numbergp, Each InT/ & a r q&MdhR All(, "QI Nex0ngS;  Sub 'ProcpNews* Public CopyPAmodTemte(PcopQ@iod tnamy{7LDGQApp at.EnableEvents Fapremove afterstingmM?'Cinllp cAu7bfilA if  mq)s(wbLDU*P/s("$LD")Ma$keTPD`CurrReg?SC@dDi stribonGrC#  fPasteSpecial0#v_ _  _ _ ok_ 0_ t_ \ $$$_x1/10FL`ineTo1Dimum Aser$ ' 0 A 7na'if h,NnSU`eBPr F;r !te` Xa`VSan "e t p"@A! Dim rngT;P+"#FBȄJ! ' u`v  !aN; so willH sa"y n oA CplacϤV' `/sg' aZ <> "3 3"=#po8c$ '! n'te a stJductC 3''a`mom`_ eW,c...D')P 'mcould chAi, Qs2?'unnecessary7QA Np?d34tZMsgBopx ("%0nallowed. T mu.bQ} EFFECTIVE PERIOrD.."> @dA3Remo7> 'DpSnts<": S ~;dac`rolfoa<OKToQbinueacCE _cDcFals. An@G mqa;nf;= _^MsgBox(prompt:="OK&(HisdU?", ButtC:=vbYesNo + vbC(rit@~lDefault1Q Hvb!, Q= %!KU=s6 a'Dfi car al numb`lJ!'&Set Z= ActzCell.rJ.R("b1V'''grabyC s= D Mu d h chas r`oDj 4fc sourc`a4amt/ϣ'1w?RnAM09Offset(0, 2$-,!!p).'dLocka 0 hUWHP'J" 'gu$so'at,grema vi~E.cFirstSgP T OdAm!1AqFqDestin":=CE6'A=bY.Z c?Q[~ '2#raQj8AddStop@D1ziPDCnt%, iLoopSpv, v0 {PqF~sVer*sw$b1PDà 'l roughas <> 100% R:ddp l deac Wb_Len(Trim(R UR)) < 1tAY` "SUSPENSE"cEx@+G3 !u.U "a ) 'a zT \L#a=iU qColHvzg.Rows.Count), 0)иtire P.Iá(xlShiftDownXMnd?Đ16qC- Numm\ f1#o 'tlPck5)4M/) A!>P*5 qg&^A _[?ebBD(ToF  =v9A2$bta K+om3 .Range("FundingEnd" & iLoopCnt).Locked = True 'rel@ modifi\cell ActiveSheet.CalculateD If0Nextj.Protect contents:=x 'now if applicable add a st@opper iod,iPDNumEffPrds").ValuKIf DRE() >" 6/30/Year) Thevn3|vk6g0cvFYAdd("y", $1,,))Els"nd [7qpEt)) 1@FJWithmrba PD#@ " !SubPu bp  Migr hWorkbook() 'Scott 11/12/03 Dim fd As FileDia`log 'g#vOldaVariant 'old f naVfull pa!a !WBN!` jwd New new  a arrWSHdrX(8)JAs headerPDeff@dm=s-n^f lcdo@r tab $ source(s,wB:ffobjh4bOpenB! lean 'flag to tesf is ocnt%,#Run% 'l ӠuriTtlFsRowsttl fs r iǾ!numbof % 'Cr>eY DTas Pir dWbox. SDetA[= Action.(mso!+bgbmu t k*= su0 i%"o{ In@ Rightt"ldf"l. |.6UB0"WTit`y= "SelA!the5$=QmBKRters.C0"115Qi "st"*.xls@j.Shp= -hCF+Each !FIn . edItem yr d.Co()p us &rJAju.one #w!yv@A"InputBox("E1/ S Na8b , " P(Left(n) - 4@{& "_V2ʼny _|Pvu GoAit_this_sk 3v <> 'do not overwite ehO 5*.P :=df߯7mF@{ C@00s("DpribuI"F5l R'. $'pxwsdi i8nfo )Vq0)_> V1YDefaultOr.g_\2_tAccto 3i4//(4ailyRhOOH5I]PS? ?86<BannperIDooh7lEmpK__X= h(TotkRmA(5--,O,pd~ ,|AcP+^o?2/G, oZ0 '젒xtOOrFJ2Nմ.Offset(0, 1).Value 'period # arrPDHdr(cnt - 1, 2) = .Range("EffStartDt" & FE13 XEndAV4 +((XFunding.XRowz)m_Z"}#)@ '# o rows*If Z0 Then 'adjust where only 1 f,iTtlFs8jsm(H+'Else_ Q7` nd If ReDim Pres0erveRxs(3, J!(iNuBmPrdsA)B)Vccoufor base 0F iLoo6pShooull f@ inf>oP@J/@iRunCnt GK$, 0fs nam$o|o} $j25bK2f%_JaCG@ 5ߡA`#?%@iNext 4{@`` With7 ActiveWorkbook.Sheets("Calculator" `get tphe cbV.Protect contents:=Fa>#u@MA+Col").Curr RegiHon.a.Cs-=)3r9= ɂrUBd( |`MndOa ='m9)Truxw_@!Each wB In s 'Nmulti-selqmePreopfw# temp"o! 63k".NF@1vNewWBkKv ().+at bOP E(.Exit P o-]mWr#r U.AA FileAb:=I;Y LKKDistributPA"<#{7\? 9N ŦDefa`6OrgMR=WS!1Wpus :s hea8derAcctr(2aeStop/r(3//0DailyROr(4OO@ldfPS?r(5??3BannerIDqor(6oocEmpL_r(7__P Tot_xr(8_Z[f=@ 2 To 'build #zjAddPޏ=j00&0?r(0?71>b4pd3_ _  a Bo=)tO/DEƳ .Range("EffStartDt" & cnt).Value = Date(arrPD Hdr((@ - 1), 2))@ 'pd sz dt  r(End.p3pend#nProtect contents:=False'TPDRWS4default r,For iLoop0 To UBoundFs"If C(3,*)*Then#iRunCnt > 2 'Ew1$ing@xNAc8tivGAddLineToPeriodg$x If L//O@ffset(G,, 0`0`'fs namE:*j11$<@H).m22%L= %+ 1: NextCb 0 `#'load the calcul4at@~fa{With eeWorkbook.Sheets("C& ")a/"()RCol"A 5b`ClearC kkAa 3!HA-;EMsgBox 6nnot use s fidleb fAvNewFamDaveAs a :=; %s(v@OldWBN ).Clo vechar1R CPi.SelPwedItems.Cmt(1/e2e  p" was successfully mig1wd"/O1Each wB6 In oE@ . <> isuCru=Trub /r Qd 0~&0% (s) h`$ bepM%  '!.r pred Cancel. GoSub exit_this_subKM;) *:3Set fd&NongSb$ PDa$ ' xME (SLSS<N0{00020820-0000-0000-C000-000000000046}@(%H0 xpAttribute VB_Name = "She@et1" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$BustomLizD2 O@ption 2licit 0   ( 8 H X h x           @( h x       (8 FH N      0 @ P ` p       0@P`p > n @ P ` p      0@::+:xME(4<6<< <( 80LʐPLʐ0Lʐ0LʐLʐ1Lʐ01Lʐ@LʐX1Lʐ1LʐLʐ1Lʐ1Lʐ0Lʐ1Lʐ 2LʐLʐH2Lʐp2Lʐ Lʐ2Lʐ2Lʐ Lʐ2Lʐ3LʐLʐ83Lʐ`3Lʐ Lʐ"3Lʐ&3Lʐ*Lʐ,3Lʐ04Lʐ4xLʐ6(4Lʐ:P4Lʐ>Lʐ@x4LʐD4LʐHhLʐJ4LʐN4LʐRLʐT5LʐX@5Lʐ\XLʐ^h5Lʐb5LʐfLʐh5Lʐl5LʐpHLʐr6Lʐv06LʐzLʐ|X6Lʐ6Lʐ8 Lʐ6Lʐ6Lʐ Lʐ6L— 7H7 X  T8   p 7L L7@ 8 @ <'( 8L L88L`8L LLLd).L 0  %  ourc%`ains`  $% 8 fB@%`B@``B@`rThi` Sc```@lo`> 10`ine piSUSPi76 77 % (ndCo%`Fu`ZB@`Col``  $$ &(  '' (riod% {{ (umma%` not``<$`2 f`% h row% 4ld%` 80`f `hcell `f%`h' DPiF theiH``J <iFiHs li` B(CCiFHiH ` :@iFiH L% N8ARED% @iFiH+ Lnt7% N$% cc`f `h`P`` `+`R`T `V`Xskip`Z`\`^ ("C`` > `f`h$`P ` ` ` `R `T `V `X `Z `\ `h `j8 `f`h5 p..If ;Ih `r fun`td so`v`xH`z`| ens`~the `ldp``Rbuti p@**If `singIh C@`r`t`v`x.`z`|ldpD`~!``und `Rge.e n0i'adi new L % N(, % l22`tota``( `j`X7``iTot`ctCo H % fhtAlp% h % lpha%  % :% \7%  P99)px%)%`B@H`at c`g, e````oWor`ldp``EXTR`leas`.`( !!)8!@!roce%)`!tion%``+X#!X"--i!B@i"undsi8"%i% x"% f"% h"cycl% "n th%`riod`and ` arr`ate `cycl+ ##% f#n wh%+x%$ ++i $i@$t$i`$ be i $a27% f$refr% h$fter% $6%`!Z`!Z`&St`m ss`a2+&% i%000ising & and% f0&d2% hP&%% p&,%`Sfo `'``&o2`act`@9``fo  '&& '% '(%+( (i@(low iH`x`d en`kspa`` ) (+mm`fw th`h` last` ta`r to`<co` dat f* to % h8*me k% X*f HR% x*er F% *and % * the%`  (*%`D  `F  ,GG`fT$`h` fon`ntir`tals` `2 `f `Treb`Regu` 5`"`$ut t+-, i&+`-@- i&dds +.- i-8i&`($6`*8+.P.++i,p. i..!0i&)`0(`28 4 / 6x/ `8 :/+/ + h0LLʐ(LʐxLʐLʐLʐLʐhLʐLʐLʐLʐXLʐLʐLʐLʐHLʐ pLʐLʐLʐ8Lʐ`Lʐ$Lʐ(Lʐ.(Lʐ2PLʐ8Lʐ<LʐBLʐF@LʐLLʐPLʐVLʐZ0Lʐ`LʐdLʐjLʐn LʐtpLʐxLʐ~Lʐ Lʐ` Lʐ Lʐ L P p7L L L7LH Lp L L L'**P /ߛ 2<@: 8  ( 8 H X h x      P (`            *( X x  2 $  @ J` 6       0  @  "P Dx          0  @  P  `  p "  4   4  R $h  " 4 R ` "x   6 ( 00` hpx      0" @ P0` : 6 8@H`  *h$  (  (6@ x :   8 P *h "" 8H*`""  *  P"h"  "*@p""  *H P ` h "   <  8Ph &8P h x  " &  @ PX`hpx >  0 4@ x     &0  N@ .X JPP P P P` <      ( 0 "P X p x  >    0 !!   !  @!P!p!x!!!! !!! !"!H" H" X" x""" `" 2" ^0# P# # $ " $ ($ 0$  4H$ $N$ >$  %8%  ,@%>p% % %% 0%>& X& p&&  Z&>& (' 8'P'  X' >x' '' ' $'"( ((( (,((X(`(h(p(  Dx( (  ("(, )2P).)")  B) T(* D** *+ + @+ H+ h+ + +  L++, , 8, P, h, x, , ", , , , - -  -  ^(- (- - - -  . . 0. H.Rh. .  . . /   / 8/ P/  p/ / /  / / /  0 (0 @0  `0 x0 0 \0112 P33 P4  @4(5 8@5 x5 55 5 85 5 66  &6@6 X6 h6x6 6  6"6 66  6 66 6 67 7 $07X7 `7*h7 777 77  7 7 8"(8 0888 @8`8 $h8 $88 8.8 8 B9H9X9h9p9" 9  T9 &9 : : (: H: `: x: :: $:":: :;  ; >8; x; ; ; ;; ; >; (< @< X< h<x< < (<"<< << < >= P= h= = == = >= > > 0> @>P>  X> >p> > > > >? ? > ? `? x? ? ?? ? >? @ (@ @@ P@ `@ p@@ @ >@ @ @ A A 0A @APA  RXAA A AA A "A" B )0B8B @B`BxB B B B B B B C  C (C"@C )PCXC `CCC C C C CD D(D@D XD hDxD DDD D DD D "D" E  N(E  xEEEEEEEEEEEE EE E  FFF F@FHF8PF8F  F  F F F $F  G 8G PG XG  `GpGGGG G JG  H D8HH HHH HHH HII I (I@IHIXI `IxIII II IIIIIJ J J0J@J HJXJ `JpJ xJJJ *J"J  bJ  0K8K@KHKPKXK`KhKpKxKKK KK K  KKK KKL:L:HL  L  L L L $L L M M  M  (M8MPMhMxM M JM M DNHN hNN NNN NNNN NNOO O0O8OHO POhO pOOOOOO OOOO P >P 2XPPPPPPPP PQQQ  Q8Q @QPQFXQQ QQQQ QQ4R@R HR `RNpRR RR2R (S 8SHS PShS4xS S SS S S2T 8T HTXT `TpTxT TTT $T "T TT TT&U(U@UPU`U hU" U PU UUUUUVVV<VXV  `V &VV V V W BWXW pW W W W W W W X 0X @X &PXVxXXVY Y   $Y" Z LZ DhZ ZZZZZZZZ<Z0[ 8[ &X[[ [ [ [ B[0\ H\ `\ x\ \ \ \ \ \ ] ] &(]P]p]]]   *]"] P] XH^ ^^ ^^^__ 8_H_`_ x___ $_"_ __`.(` X```h`p` x``````` *`D`(a@a Xapa 6a,aab(bHb &`b b bLb *b(c8c F@cccc cc &c "d dd  d@dHdPdXd`dhd pdddddd  d d  Bde8ePeheeeee Def0fHf`fxfff  8ff&g&0g&Xg&gggg  g"h 4  h(h   0h @h Ph>ph>h>h>0i>pi>i i  iB$j 0j8j@jHjPjXj`jhjpj xj$jj:j "k @kPk$XkVkkTk @lXl F`l l l llmm8mHmFXm mmm mmmm mBn Znpn :xn n nnPn 60o hoo "oooo B$o "o p p(p0p$8p$`p$p(p,p q(q:0qpqq qq qqqq :r Hr hr :r Dr 8s ^Hs \s 0t B8t t  Rt ttu u B u 0u8u@uHuPu Xu:`uuuuuuvv0v@v Z`v <v \w 0`w,w ww w "w wwxxxx  : x R`xxxxyy0yHy .`y0y:y.z.0z `zhz"z Lz zz $z{>0{>p{>{>{>0|>p|>|>|>0}>p}>}>}0~ 88~ Jp~ N~ ,  P X` $  B   ( 0P X x. ؀ * ( 04H. ȁ؁"(8@"H PX`hp x   ȂЂ ؂   ,@rH ؃  4P( x   : X &8$`$  ȅ 0Ѕ .08 H$` .  ؆    08 P X h  r   @ H X p  < 06 6H  @ Љ   0 @ P` h  p" d . 4( ` P Ћ h nH  ȌЌ،  (  0 @`$h2ȍЍ.؍ &48px0  Ў ( ,@"p,ȏ   ( 48 p   Ȑ&ؐB  R 8`   B  P :`  Ȓ  B   (H` x"  Bȓ  D >( h J Rؔ 08 @ H ,h .ȕ F B@ 4 $Ж$(  0$@h$p  ,(З   " 0"8@ H` ȘИ"ؘ" B D ` x :ؙ B8(` hx]@0need to make this 8 for rollout]h0)the payperiod # is the index to the array](the max number of lines in an old or newdistribution (for PHAREDS)2Column constants for LDP: PayPrd Totals Worksheet]$0]$(]$P]$0]$x]$0]$]$]$ 1 ]$ ]$ 01]$  *Column constants for LDP:Extract Worksheet]$@]$X1p id]$hewLi]$1]$]$'id]$1ell ]$VE P]$ 1]$ >us]$ 0fy t]$ 1be s]$ Xing ]$ 2]$!fo]$D am]$H2lls]$he n9Column constants for LDP:Distribution fund summary region]$p2 nam]$fo]$ ]$2St]$Htive]$2.]$pd ]$Star]$ 2vePe]$B]$C3or]$F.F]$GkD]$H83& ?Column constants for LDP:Distribution pay period summary region Ginclude letter identifiers for columns that will have formulas inserted ]$83]$`3=Sal]$`]$]$3]$rive]$ 3%]$  ]$ rd ]$       !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdghijklmopqrstuvwz{|}~3(E]$ ( ]$4 ]$P]$x ]$(4nge ]$ERIV]$FP4ls]$Gn ]$Hmu]$Jx4]$K/0]$M4]$N@]$Oh%]$P4]$Q]$R48Column constants for LDP:Distribution work space region,gincluding letter indentifiers for cols that will have formulas inserted (also in ldpMain in ldpast.xls)]$ ]$ SalF]$ 5lFor]$ "]$@5]$0P]$XessN]$h5]$]$J5]$K]$L]$M5]$N]$O5]$P ]$QH]$R6"Column constants for PHAREDS entry]$p]$06]$]$]$X6]$]$6]$ ]$ 8 ]$ 6]$ ` " this is the last column]$6 light gray]$# ! light red]$&  light green]$6% this is the row from which we'll<copy the name, iD, posn, etc.] :Array to hold key PP data (not sure if we have everything)]  714 an array to hold th enumber of units per pay period/array to hold all PP data (supplants the above)]  ]H7]p7] ]7] array to hold PHAREDS lines=array to hold the distribution lines for PHAREDS, old and new]  ]7]7]H ]8]p ] ]88] ] `8]  ]  P -for each payperiod in DISTRIBUTION PP Summaryif extr amt <> 0 -for each fund detail line in old distributionKprint pay id, units, pct, amt, coa, index, fund, orgn, acct, prog, earningsLeave PCT null for the momentprint totals linehighlight all in light gray -for each fund detail line in new distributionKprint pay id, units, pct, amt, coa, index, fund, orgn, acct, prog, earningsprint totals linehighlight all in light green] ] ] ] ] ] ]@ ]X 0locals to hold values of pay period summary line]p ] ] *locals to hold values of fund summary line] ] ] ]] PHAREDS rowldp$|@k8 Distribution$)assign ranges, rows to pay period summaryldpDistPPSumStart76. !0' $$No pay periods in Pay Period SummaryA@3not sure how above would happen; if there's data in/fund summary, shld be data here but if not exit|k %!<$ 'd  %!0'k#assign ranges, rows to fund summaryldpDistFundSumStart76. !0' $No fund rows in Fund SummaryA@| k "find starting cells in PHAREDS tab PHAREDSStart PHAREDS Entry$%6!0' %!<$ 'dX  %!0'k8     "78- there is a PP dist to report  78'  78'  78' #process old PHAREDS in FUND summary     $4 process line  +Y +020 + ldpAccount76!< + $  78'     $ +  78$ +  78$ +  A  'k k  #process new PHAREDS in FUND summary     $4 process line  +Y +020 + ldpAccount76!< + $calc a daily rate:  ^78  V78'     $ +  b78 +  78$ +  78$ +  A  'k k   '(skip line between setsk qMsgBox ("Coming soon...")oPreparePHAREDS06write PHAREDS array line to PHAREDS tab at Here_In row PHAREDS Entry$ $  v;8 $  x;8 $  z;8 $  |;8 $  ~;8 $  ;8 $  ;8 $  ;8 $  ;8 $  ;8 $  ;8  v78  78$6 Trebuchet MS5(Regular5( 5( 5( 5(q`qXo WritePHAREDSfind place to insert a new row;adjust the totals row9make sure that fund cell is unprotected so user can enter]]]0]H]`]x]7first check to ensure that user is in the right contextldp$|k Distribution$*now unprotect the sheet and protect at end-turn off events processing and turn on at endC@ .($ . %!ZB@2 %. !0' !0'ldpDistFundSumStart$6!0'  'Gput value in fund column to enable range.end to work in subsequent codeNEW FUND  $8(<  $8(8'add the networkdays formula to new line  =networkdays( d ,  f )  V$8( Dadjust the totals column (we have removed the totals of the pct col) =sum( j : j )  X78(  .Cells(iTotalsRow, LDPDistUserPctCol).Formula = ' "=sum(" & LDPDistUserPctAlphaCol & iStartRow & ' ":" & LDPDistUserPctAlphaCol & iEndRow & ")" =sum( n : n )  \78(  =sum( p : p )  ^78(  =sum( t : t )  b78(  B@H+ leave user at cell to enter fund/org, etc.reset the contextC@ .(qoAddDistLineToWorkspaceldp$|k 1EXTRACT tab is empty. Please cut/paste MR2 data.A@|0k( $EXTRACT data has alreay been loaded.A@|kLooks good...proceed .( Distribution$B@A@A@A@A@A@ CalcDailyRateForFundA@A@  .( Distribution$B@ldpdistfundsumstart Distribution$%6%B@H "($Application.Calculateo ProcessExtractZthis is forcing us to cycle through the funds again; in the future, process the pay period,summary first and put everything in an arrayWcalculate the daily rate when cycling thru the funds the first time, using the PP unitsJfrom the pay period array; then when done with the fund summary, write the PP summary from the arrayo CalcDailyRateForFund(]hExtract$.this shouldn't be protected...but just in caseC@a276$&3move last refresh date to 2nd blank line after list a3$6%%la276!ZB@*a276!ZB@Xk&Strip single quote from ssn and formata276 a276%Extract$%6'.0C@, 000-00-00009q)strip single quote from suffix and formatd276 d276%Extract$%6'.0C@,009qxSformat grant,fund, orgn, acct, pgm, activity columns as text and strip single quotej276 o276%Extract$%6@9'.0C@,qformat pgm code as "000"j276 o276%Extract$%60009q0q(o MoveRefreshDateandStripQuote]]]]]0row below workspace totals row]H]h]]>seeds start and end dates, formats workspace and adds formulas Distribution$init local varsldpDistWorkStart76!0'$ !0'0row for workspace totals  '24row below the workspace totals  '2last row of workspace data '$shorter to use here<copy the start and end dates from fund summary to work spaceMone-time kludge relates to inception of HR Banner; can be removed after FY04>remove the IF and just do the instantiation in the ELSE clause     $8 6/21/20037/1/03  R$8(<  $8  T$8(<d  $8  R$8(<  $8  T$8(<kp Eassign font and font size to entire work space incl totals and target  R78.f 2 b78.h f h$6 Trebuchet MS5(Regular5( 5( 65(qp gray out the calculated cols  ^78.f  b78.h f h$6 5( 5(q Wformat the data for each column -- when user adds new lines, pgm will need to fmt these !add formula to networkdays column  R78.f  T78.hm/dd/yy f h$6(  V78.f  V78.h0 f h$6( ( f h$6!8  =networkdays( d (!0,  f (!0) ((  (  X78.f  X78.h#,##0.00 f h$6(  ^78.f 2 ^78.h#,##0.00 f h$6(  Z78.f  Z78.h0.00% f h$6(  `78.f 2 `78.h0.00% f h$6(  \78.f  \78.h0.00 f h$6(  b78.f 2 b78.h0.00 f h$6( Vadd sum formulas to amt, pct and units -- nix the pct (and nix the user amt/pct/units) .Cells(iTotalsRow, LDPDistUserAmtCol).Formula = ' "=sum(" & LDPDistUserAmtAlphaCol & iStartRow & ' ":" & LDPDistUserAmtAlphaCol & iEndRow & ")" .Cells(iTotalsRow, LDPDistUserPctCol).Formula = ' "=sum(" & LDPDistUserPctAlphaCol & iStartRow & ' ":" & LDPDistUserPctAlphaCol & iEndRow & ")" .Cells(iTotalsRow, LDPDistUserUnitsCol).Formula = ' "=sum(" & LDPDistUserUnitsAlphaCol & iStartRow & ' ":" & LDPDistUserUnitsAlphaCol & iEndRow & ")" =sum( p : p )  ^78(  .Cells(iTotalsRow, LDPDistCalcPctCol).Formula = ' "=sum(" & LDPDistCalcPctAlphaCol & iStartRow & ' ":" & LDPDistCalcPctAlphaCol & iEndRow & ")" =sum( t : t )  b78( :add target formulas for amt and units and highlight in red 2 ^78 =   -  p 9  5( 5(q`  2 b78 =   -  t 9  5( 5(q  highlight intervening PCT column 2 `78 5( 5(q qx o PrepareWorkspace]] .(A@A@ PayPrd Summary$C@A ldpPPSumStart76!0'f f 78%$ f'hd  ldpPPSumStart76%!0'hk` f hA: f hA<C@q( .(Application.Calculateo LoadPayPrdSummaryFirst0]p] Distribution$A>ldpDistPPSumStart76!0'f f 78%$ f'hd8 ldpDistPPSumStart76%!0'hk; Call LoadPayPeriod_UpdatePPArray(iFirstRow, iLastRow) f hA@ f hABqo LoadPayPeriodSummaryNload the extract PP array with the number of units per payperiod, using the PP number as the index to the array] ]8 Distribution$  F H  $8!<'J  &$8!< J+ qPo LoadPayPeriod_UpdatePPArrayDP] H 'PayPrd Summary$  78 =sum(  F:  H)9  Trebuchet MS5(Regular5( 5(#,##0.009q0  78 =sum(  F:  H)9  Trebuchet MS5(Regular5( 5(0.009qqxo LoadPayPeriod_UpdateTotalsFirstD]h H ' Distribution$  78 =sum(  F:  H)9  Trebuchet MS5(Regular5( 5(#,##0.009qX  78 =sum(  F:  H)9  Trebuchet MS5(Regular5( 5(0.009q  078 =sum( F F: F H)9  Trebuchet MS5(Regular5( 5(#,##0.009q  478 =sum( J F: J H)9  Trebuchet MS5(Regular5( 5(0.009qH  678 =sum( L F: L H)9  Trebuchet MS5(Regular5( 5(#,##0.009 5( 5(qx?  :78 =sum( P F: P H)9  Trebuchet MS5(Regular5( 5(0.009 5( 5(q>Khighlight the intervening PCT LEFT cell, even tho there is no value in cell  8$8 5( 5(q>q>o LoadPayPeriod_UpdateTotalsD]] PayPrd Summary$ F 78.L H (78.N L N$6 Trebuchet MS5(Regular5( 5( 5( 5(q<q<o LoadPayPeriod_UpdateFormatFirstD@]] Distribution$ F 78.L H 478.N L N$6 Trebuchet MS5(Regular5( 5(q; F 78.L H (78.N L N$6 5( 5(q; F .78.L H 478.N L N$6 5( 5(q;q;o LoadPayPeriod_UpdateFormatHprocess extract to summarize PP information and move to "PayPrd Summary"]@]X]p]]]]]]]]0]H]`]xExtract$ init vars 'P 'f  f P78%!0'h f'R f'T ldpPPSumStartPayPrd Summary$%6!0'^ ldpPPSumStartPayPrd Summary$%6!b'` T h T'R R P78!<' 'V 'X  prime the WHILE loop R 78' R 78''Z'\ V X Z T 78 'Z \ T 78 '\ T 'T T P78!<'V08 R h.8place pp summary in Distribution worksheet unless we are@through looping<=add formulas for calculated pct and units in calculated spacePayPrd Summary$C@ ^ `78 9<09 f9dq7 ^ 78 9<m/dd/yy9q6 ^ 78 9<m/dd/yy9q6 ^ 78 Z9<#,##0.009qh6 ^ 78 \9<0.009q06 ^ 789<0.00%9q5 ^ 78 Z \9<#,##0.009q5 ^ '^ T'RC@q5k5x5qp5o !LoadPayPeriod_ProcessExtractFirst\process extract to summarize PP information and move to PP summary section of "Distribution"]]]]]0]H]`]x]]]]]]Extract$ init vars 'P 'f  f P78%!0'h f'R f'TldpDistPPSumStart Distribution$%6!0'hldpDistPPSumStart Distribution$%6!b'j T h T'R R P78!<' 'V 'X  prime the WHILE loop R 78' R 78''Z'\ V X Z T 78 'Z \ T 78 '\ T 'T T P78!<'Vh2 R h.8place pp summary in Distribution worksheet unless we are@through looping<=add formulas for calculated pct and units in calculated space Distribution$ h j78 9<09 f9dqX1 h 78 9<m/dd/yy9q 1 h 78 9<m/dd/yy9q0 h 78 Z9<#,##0.009q0 h 78 \9<0.009qx0 h 789<0.00%9q@0 h 78 Z \9<#,##0.009q0 h *787after fy03, remove the IF statement and just assign the+PP start date to the real start date column 6/21/037/1/039<d@/ 9<k0/m/dd/yy9q/ h ,78 9<m/dd/yy9q. h .78 9<09 f9d= Networkdays( B h, D h)9 qX. h 0789<#,##0.009q . h 2780.00%9 = F h /  < h9 q- h 4780.009 = F h /  < h *  @ h9 q8- h 678#,##0.009 = < h- F h9  5( 5(q, h 8780.00%9 = > h -  H h9  5( 5(q(, h :780.009 = @ h - J h9  5( 5(q+ h 'h T'Rq+kx+p+qh+o LoadPayPeriod_ProcessExtract ]`]x Distribution$AlldpDistFundSumStart76!0'f$ !0 'h f hAn f hApq*o LoadFundSummaryJEnters summing formulae in the totals row of the fund distribution section]](]@]X]p]]]] .number of units for entire PP; not just foapal] Distribution$ calculate amount and unit totals R f h v R 78!< 'v x R 78!< 'x R<calculate each fund/period's percent of whole and daily rate R f h R $8'r R $8't r t$' R $8'| R $8' | v'~ ~ R 78(< |  R 78(< z ~ 'z Rfill Total cells on spreadsheet  =sum(  f:  h) h 78(  .Cells(iLastRow + 1, LDPDistPctCol).Formula = ' "=sum(" & LDPDistPctAlphaCol & iFirstRow & ":" & ' LDPDistPctAlphaCol & iLastRow & ")"  =sum(  f:  h) h 78( q&oLoadFundSummary_UpdateTotalsFthis version cycles through the fund summary amount and units columsn,>calculates the totals, and writes total as value in totals row]]]]]]]](]@ .number of units for entire PP; not just foapal]X Distribution$ calculate amount and unit totals R f h v R 78!< 'v x R 78!< 'x R<calculate each fund/period's percent of whole and daily rate R f h R $8'r R $8't r t$' R $8'| R $8' | v'~ ~ R 78(< |  R 78(< z ~ 'z Rfill Total cells on spreadsheet v h 78(< z h 78(< x h 78(<qH"o!LoadFundSummary_UpdateTotals_origDpJupdate the formatting on the ldp "Distribution" sheet, fund summary regionRfind the region and gray in the cols except for fund -- flash...now including fund]] Distribution$  78.L  78.N L N$65($ ' light gray 5( Trebuchet MS5(Regular5( 5(qp qh oLoadFundSummary_UpdateFormat0]pfrom extract row]from extract row]from extract row] calculated from extract row data]]]]]0]P]p]]]]$Process down the Extract Fund columna2 Extract$%%6.fFexclude hdr row  f%.h f h$6.Extract$  0instantiate vars with data from this extract row !<  !0 78!<' !0 78!<' !0 78!<'  !0 78'    $write initial data from extract$ .  (< !Z%B@22*create another blank line for next fund soL#we don't scribble over pay period s  Ad?transact additional data from extract line against summary line    $.  Ak@ q(oLoadFundSummary_ProcessExtract4]]Distribution local vars]]] ] ]0 ]H Extract local vars]` ]x ] ] ] !0' !0';assign values of current fund in Distribution to local vars Distribution$  78'  78'  78'  78'  78'  78'q0>assign values of matching fund in Extract detail to local varsExtract$  78'  78'  78'  78'  78'qP1compare to Extract values and process accordingly Distribution$   G   78(<j   G   78(<j   G   78(<j   G   78(<j    78(<    78(<qoProcessAddlFundData< ]h!]! !0' !0' Distribution$   Extract$%8!<  78(<   Extract$%8!<  78(<   Extract$%8!<  78(<   Extract$%8!<  78(<   Extract$%8!<  78(<   Extract$%8!<  78(<qo ProcessFirstTimeFundData ( ((!]`"]"]"]"]"]"]#](#]@# Distribution$ldpDistFundSumStart76.f f!0 78' f!0 78!< f!0 78!<' f!<    f.d f%!0 78'  f%!0 78!< f%!0 78!<'   '  f%!<       f%.d f%.f1' we've already checked the 1st two rows  f%.h f h$6.   !0 78' !0 78'  !0 78'   '   '  !<       .zHk@ k(k qiPutExistingFundHereX#Sexamine "Distribution" fund summary listing of funds to see where to put a new fund]#ldpDistFundSumStart Distribution$%6.f f$ f. d  f%$20aaargh Ctrl-down does not work for a region of 1H/so, if there is only one fund, don't use xlDown f%. dx  f%%. kHk@iPutNewFundHere ( ''#sFund_in holds fund and org]$]$]$]$]%from fund summary row]%from fund summary row]0%from fund summary row]H%result of comparing PP amt]`%result of comparing daily rate Distribution$'ldpDistFundSumStart Distribution$%6.f f!<$ f%$ f.hd@  f%.hk  f h76.   3instantiate locals with value from fund summary row !0 78!<' !0 78!<' !0 78!< !0 78!<' >if the fund is the same AND if the Extract pay period (iPP_in) 1is continguous to the Distribution pay period AND Wthe amt per pay period, i.e. the daily rate, is the same, then don't create a new fund. Uwhen the fund exists but starts a new period not contiguous to the prior period, then )we want to display a new fund/period line   '..still need? I'm using the daily/unit rate now   '  !<       G'j k q iNewFundOrNewPay(x%]%]&]8&]X&]x&'ldpDistFundSumStart Distribution$%6.f f!<$ f%$ f%.hd  f%.hk f h$6.   !0 $8!<'Tif the fund is the same AND if the Extract pay period (iPP_in) is continguous to the6 Distribution pay period, then don't create a new fundUwhen the fund exists but starts a new period not contiguous to the prior period, then)we want to display a new fund/period line !<   G'j k(iNewFund&]&]&]']']0']H'4Enter the person-position related header informationKInstantiate local vars to extract values; then assign to Distribution cellsExtract$  78'&  78'  78'  78'  78'  78' & ldpEmpName Distribution$,6  ldpBannerID Distribution$,6 - ldpPosn Distribution$,6  ldpEarnCd Distribution$,6  ldpAccount Distribution$,6qo LoadDistributionHeader`'Ecopies relevant columns from Extract to PayPrd Totals for subtotaling]']' PayPrd Totals$B@Extract$  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&  PayPrd Totals$%l 7B@&q PayPrdStart PayPrd Totals$%6!$.     D, B@  PayPrdStart PayPrd Totals$%6!$%!8.   !<*TotalG !Z!(j  #,##0.00  PayPrdStart PayPrd Totals$%6!$%!8( PayPrd Totals$B@o LoadExtractSubtotals']`(]x(](](](PayPrd Summary$'x ldpPPSumStart76!0' ldpPPSumStart76$ 'dX ldpPPSumStart76%$  'd  ldpPPSumStart76%!0'k~k~     78   78   &PayPrd Summary$%8!<' x  'xk8~  x'q~i GetPPTotUnits( distribution$ !ZB@Xq}o})]X)]p)])])])])])]*] *]@*]`*]*]*]*]*]*]+ldp$|}k} .(%have user confirm in order to proceed'D @Fund rows that are not highlighted will be DELETED. Is this OK?N R T V P$'F F XG'Dj D||k|-if all is well, then continue with subroutinea1z100scratch$%6B@\2oooh....this is very very bad Distribution$C@4find first and last row of fund summary distributionRand first and last cell of fund column; not sure which one will be most useful yetLDPDistFundSumStart76!0'fLDPDistFundSumStart76.f f $8%$ f'h f.hd0z LDPDistFundSumStart76%!0'h LDPDistFundSumStart76%.hky h ' .!!ZB@&A1scratch$%6B@( f h Distribution$%6!ZB@6q@yScratch$A176. !0'  %$ .dx  %.kx !0'     ' Distribution$B@LDPDistFundSumStart distribution$%6l !Z !ZScratch$%6B@&qw Distribution$C@ f  '  $8.f h $8.h f h$6!ZB@X6in order to refocus and activate one of the date cells*figure out which one of these is essentialLDPDistWorkStart Distribution$%6B@HLDPDistWorkStart Distribution$%6B@:coerce a recalc of the daily rate by CHANGING the end date .( f T78.f f   T78.h ( f h$6 (!<' (B@6  ((< (quA@o SelectUserFunds(+]this reloads the pay period section of the Distribution tab to reflect the fund distributions'that remain in the fund summary section-for each pay period in the pay period sectionfor each fund summary lineJif pay period of fund summary line = pay period then add amt to pay periodend forawrite the payperiod total (it could be zero if user has deleted all fund lines associated with ithif it is 0, then put zero in user pct and unit column (rather than keep formula that causes div by zero)end for]h+]+]+]+]+]+]+],](,]@,]X,]p,], .( Distribution$C@LDPDistPPSumStart76!0'fLDPDistPPSumStart$6%$ f'hd(r LDPDistPPSumStart76%!0'hkqLDPDistFundSumStart76!0'LDPDistFundSumStart$6%$ 'dq LDPDistFundSumStart76%!0'kHq  f h  78!<'  78'  78'''       78   78  78  V78'"   "   $ '   78 ' kp    "$8(<   &$8(<   $+8&zero out the extract section  2+8  4+8kXo C@q8o .(oLoadPayPeriod_fromFundSummary,Kif this is one of the original lines from the extract load then return true2else, this is a user-entered line and return false Distribution$' & 78$G'jqniThisIsAnOrigDistLine-Iif this is a redistributed line (new or old) there is a value in the calc3amount field. If not null, return true, else false Distribution$' & ^78$G'jqmiThisIsAUserDistLine`-]-]- Distribution$ & 78'( & 78'*'  (  *G'jqPli InThisPayPeriod .>returns the number of workdays in user start/end dates of fund8summary row of iRow_in for the pay period represented bydtStart_in and dtEnd_inD????????????is it possbible for the work start/end dates to be null?Kif so shld probably check and assign fund start/end dates if former is null].].' Distribution$&PP start/end dates are input arguments'fund start/end dates are assigned below & R78'0 & T78'2?if fund start date within PP start date, use fund date, else PP;if fund end date within PP end date, use fund date, else PP.put results in scratch 2 to calc net work days 0 , 0A1 Scratch 2$,6di ,A1 Scratch 2$,6kh 2 . 2b1 Scratch 2$,6dh .b1 Scratch 2$,6k`h%return cell with net work days resultC1 Scratch 2$%6!<'qhiDaysInPayPeriod.Aog /]`/ Scratch 2$7/22/03a176(<8/01/03b176(<c176!<'8 8A@q0go(gx/8/02/038/15/03 Aof/=makes sure that there is data in either of the first two datacells in EXTRACT'extract$a276$a376$G'jq fi NoDataExists/' Distribution$ ldpDistFundSumStart76$G'jqeiExtractDataAlreadyLoadedpeAttribute VB_Name = "LDUtilPast" Option Explicit Const FormatSizf8 'need to make this 8 for rollournMaxPPArray30 9the payperiod # :indexQ- a1{mCHA0REDSHF20 '$ number of lines in an old `new r'Xdisz(_P4) 'Col umn cantsoLDP: PayPrd Totals WorksheezPub ~0PPID&]1  2StaPrtDt3End4Gr@35Fund6OrgC7U(Acc+8tvC9EarnC #10U UnitJsC1YAm#1YYxExtractYvSpriden&EmpPwPosvSuffCPuYeart:9O9aO9!HOQ9A'O8!O8iN1Q)Pgm1)AcR1*gI*V*WV*YRDIfs summary regO!)?:S:)!PPc*P*c#P*!F*0*"ʼn))0T!RateCTDtAlphaC"B L"#ICT@F5 c G HG@qnw w-$'include le ttpuMtifiTeroc"qsat will haveqmulawserted&PPYP6O(/(#8xPP#zPo#iccO#ealZ4MD} R`X MADaylVarMQNX/N1@Lefs/9P(_9 bH9@1AI1A i1;PP4J6 KyMsyNs O)P4QRFaQ2ƁFwp sphace4j,Fing6FnLFF)F (oldxpMap p.xls[st5rD:DP a:D/KFWrk:t:rU QDKFDtFRdotbCalc;F;F ;bVks44Si(L+8?8bo88,8.8tvt entryxc Const PHAREDSPPCol = 1 Publi Units2(Pct>3>AmE>4>COA?5QIDX6F(und 7 Orgn 8 Aca"9Pgm 10 tv1 '@ this t he laco@lumn GrayHighlEt5 'g een3red Red 8,@"ldpExtrStartDataRow#(2 'I2rfrom which we'll 8'copy@Ke name, iD, posn, etc. Dim &DistEndWork&As Integer1'ArK to hold key PP d2 (not sure if, have every6ng)PayPrd@(1 To MaxPPB) @Single@;' an aJth enumber of u ppayiog"' all)supplantsCaboveN&@"%, *5'VariCd@PPu*D!`{8v @`z# y,pRatex   lines!ed `W  g f detail;A !:>a'print aiid,#kct,A, coa@ndex, E@rogarnwGO'LePCT nur"!R@moment6t`otalsphA}*) {$] m$##eDH'mgrfǘ;rngIURangeIliFirstPP-`AbiLPv17rPm hlocA!hvaluesQa+5s4# T @Jmqdt yd U8r [Fn R kvjkiWriteHePw '%` qIf N@zInTheRq#~book("") pExit L$ QIf`7 WiwSheetXs("WEd"kNassign r&s, PvorE{A= SElsP1w.0#(xlDown)'$& '""c$"4:N"o"LE"b"""" row"22{I"'fiss cel nck;tabH>= 7UEntry"`6("d2G[1_!e߃c!!t!R !$FШi'q&c .C(i, LDPr)M{5aA̰orlib&{pdNq1;c PStartDtCol) dtThisPPEnd = .Cells(i, 8LDPP 'process old PHAREDS in FUND summary bFor kmiFirstFundRow To iLa 2 If InayPeriod(i, k) Then lineB#distArr(DPP)C#"COA"Y" -gm020!Acct.Range("ldp ount").Valuf^IsAnOriguiL|(SAAr]ate?D}kB}}R QAm.G* DaysInPi _W"E, v!4PLeft(ǎ4A4, 6L4C!ȆOrgnRigh6Call@ WriteD(GrayHl, iHere% AF + 13FAIfNepxt kkjnew?|(?| ~ ?|?|B?| ?|?|I?|!d"$|U8ser|aa'calc a daily r z: 7`Xc`|g /EćWorkWrk@~!w ,_(_}"P#_bUnitsa|΅ae!a $<deenHHHe!KHOEF H hQO 'skip ~ betw setsWIi With 'MsgBox ("Coming soon0..."XsSub 'Prepar\ A2X%]`Hor_ Asteger, N+3]p'w`fTar` toVtab at 1_Inp row 0 HShe(" EntryA 1,,PP"/Qa 5=</lr Pc n6 5F=YB_W[ tq\_WhIDX_tP iW!>ox<t>_ 1ctdwPgmotP_ X Actvotvt32(s}&,o%e42+Font.NamѨ"Trebuchet MS.B!AStylRe gularNSizPmat Intr.orIndexp*:r.Pat nPxlSolidCwD9EQRCB3CAdd{ToQs@space(p'fipl P@tin~t{?;Jadjust thetalsACA'makePr`hCf c@@ is unt ectedM uP can e! Dim @rngNew- KbTożiB!Nim i_pD fQ checkensu"X0ht c%0 NotBeRAbook"#pxit p  r/WributionW'n KrsaZ ar&oa!]en#'turn off evs $1hmn+PD n`ts:=F` tA@pplica.EnableE= QS0e PSet rngTotalsLine = PutNewFundHere () .Offset(0, 0).EntireRow.Insejust t@?tn(we hav@n0emovLofi pcmlI$.DbpUAmtH5=sum HD GKAF:HFPDd& D '{PcqA "@M",d{Units?1b1h nJCalcJ$0_Sb!1(bj1n# !ESele@' leu@{ a@'er/org, etc.c*'re`墉@contex.0Prot nt@s:=TruApplicatiLonEv s` With Sub 0'AddaTo!spac Pub qcessExtractB~ If Not InTheRight!book"t") pnS Exit3IfS$DaDtastsMsgBox ("EXTRACT tab is empty. Pse cut/paste MR2 d."W?4 AAlreadyLoad\ Gq_s ay been lb'''Ls good...proceed14vxSheets("ribusaUn!!MQ0iRef#h eAndStripQu`#PayPeriod_;pFirs&aH@er'marzyd b[4DailyRateCPrepare8'p'w,.on33/"d;ws1).3?D ctiveWindFreezePanew8]c' !`tR7 9 '  nd9'th1 pcing us1Gcycthr0oughbE a`gain;1afuture, )sЏpay p"'q f" a`1everyan arraa!'cPhb 2d1 :wh3wQ 1 p uH t$imusCe 0PP uyfr\omB s;Qn dow O1 $ , wri e n< P8S8!``Dim As R&cS1Z %)0Ma0shoulpdn't0Ia.eFPB r`casw.d=F]I$sEV(.t0a2D")] 'mF l@W rFqWe&2P!blank aftnl3 /1IC0Destinl:=3 7P(xlDown)2"D`y5e!Z1 ' a'P3qSr &ss- AaRct 4, ld .Repl@x`,at:="'̰R!m0}1.Num8berհ0000-"00A s*om suffix and format With Sheets("Extract").R ange("d 2"), .End(xlDown)).Replace what:="'",D rmen" 0NumberF = "00!]h 3 ' grant,fund, orgn, acct, pgm tivity columns as textstrip single `quote."j ot@ ]zde<@UUT~{70r-OySubA'MoveRefreshDat*eSoQmPublic A PareWorksp(ĨDim Cell As iStartRowIntpegerH@#T otalsarDgeL 'r@bel@w* t GrngB&,I2@ ia# 'seeds sa ce d@-ds,s adpulasgDilbut0ion"J1:init localp var!1=ldp !""owb0= PutNewFundHere( - C/ /I f6= i + 1c(9th`9 @6AEj -g '@*bt@ of1ah  'shorter J us8e h` '''c0opy 0? fr ammar5 o Bkone-time kludgelaGincep?aHR Banner; can bAm{d af!FY04 reIFRjust doBinntia inELSE clag iJT8o i(:Ifs(i, LDPNMDtCol)a6/21/2003" The~nAV!. Value7/1/@m # Elshm  st  -h? {? =~?  ` If'N{i'assignJn3}fosizfe'ir8* (lZ$NYSe@7VpGD 6! TDCalcUpOs?bS×[2ГngOtS@.F`. Na6 Trebuch MS=1AStyPRe gZrnt.Sy= Ә .acrior.Pat:n@,xlN?x 'gray out:_c teda(`19ll51Am":a 72OLorInde x@GHigh>l0@= xlSolid @Up\b{` each $--enar {new lineP~win3fm D'Fm@anetdxayst-.O5SVO5 O0 =9_ ;5/5"5. m/dd/yy1  Fe BWrkDay  p"*!pE  In( F{aGrrD=(" &rvDtAlpha_\?3")|BbAC(wUserD@ {֡lLB#,##0.:! ((DPDistCalcAmtCol) Set rngEnd = .Cells(iTarget@Row, LR ange(Start,).NumberFormatY"#,##0.00" ~J UserPc&otalsfd%Be(eoeAeUnitseeT3?fDj3f3['add sum f@ulas to ampct a@u!2 -- nix the (!uia/ /c'Ƅ26.}@w_z "=p(" &%E AlphaFi|`# j:[}")@dh@M "LiTg11bU1P.n b2 $+0T!{:at),BPc!EL_ Q1O io u_  ~1> =WQ'RRNQAPhighl0t in redGWith "= LP=P" -?+Ag .Interior.orIndex[RedHGPatnxlSoli A /rq&;Ox K"o n/Vl  W' *vening PCT column?A!!O = !P !:3 PQSuba'PrepareWork spaceAPu blic !LoadPayPrdSummaryF`irst( Dim i!As !gerhLas #App0ation.EnableEi@ts 0Fr  MoveRefreshDateAndStri`pQuotta E@xtractP tb%2Sheepts("  "1 .Prco?`_A_A5 _AC _AC_An _At6s_+sH?A A#rm?+?APw?A?AD**?A**1**?A (RWr:?W ={` ?AD?0WWAW>?AK?A?W,,PPVar)mW0unWn #,a;,n, Y b, /(,OK-OnoooonOnDn/,#(,+Lef1˴) .Formula = "=sum(" & LDPPPAmtLeftAl@phaColXiFR_in_ )":pLp")"nt.NameTrebuc@het MS(FontStyl-(Regr(Sifz#at vN`umber<#,##0.00 &Interior.orIndexRedHig4hlt ,Pa tnxlSolidEnd With  .Cells(iTotalsRow,UnitsƀBpo{p.po L .oC" q'h the ivening PCT LEFT c@y, e o r@ s no valuA LPc鐆2??dW"!Sub 'LoadPayPAid_U`pdatec\DPublic qtFirst(As ger, Бe'i#\' Lmz Dim rngStartion Ran|ge @#bSheets("&rd@#mmary"hSऌ =FB‡DistFun~d DG $RW 5 " B(,A 'c 0s`Gray?/3d !ah\bmaaL0" P00#0,ribut50j70DP0 0F40Varbq0h0 0+0oro}u0F(YK ФDDonR?zGm=6/}= /&uH]WorkDays+"%OteO pObO6OO '$*%"/N%#N?NProcessExtr8actcd'p s e to `rPPfaB amov~eA=uQKib QILast PP iThis s77dtb8Dw!rxNewc C0hecksng P Sl82SW Sum  PP+R2d#aI'iY0rss1i=֡v = ldpa O%!A@, C).(xlDown).'S=  += .V!"ldpPPSumStart").Row iCol = Sheets("PayPrd maryange(jumnWhile iNew <= iLast x0 iThis/.9PP.Cells(0, ).ValueO 5JPPCheck ' prime the WHILE loopdtVHLDPExtrDtN 8Endsng?Amt0 -UnitsK iC@C f + cm, >'=J(VŚƎ + 12 :C,XWendKI f Ş Then8place pp sumA in Distribution@ works unless wpe ar k'throughB8ingA ''add formulas calcted pct aXuM h sp oWithS.Prote@ contents:=FalsT% ;tum`?G%Cf҅" .NumberF`#ap"0"@.HorizalAlignm xlRightArP6jSm!m/dd/yy4"?c? I ;`&< f !n+#,##0.0+ _  _ P728T(\v B9_ _ q Pc?}} Ohv %?3 v_ RateQD ) / K{5^'roZ3&PKbP)h=Tr} UIfGSubQ'Loaderihod_0pcP1a@ctFirsPcP ublicb 3()'pdro eP toize 4inQa1~mov o%s@yof ",`Dim i2 As I|ger PP *Q~kD.'(`!"Q o`m45Sl$6 P!'1rC4'"T$ 3'i vars705^v = #ata0 %!=ƥ3(xlDown}q1eP %Wջ:Rr  ,51Zp? ?gq  iThisRow = iNewH PPx.Cells(, iPPCol).Value j'hZPP :Check  ' prime the WHILE loop 7dtPPStartLDPExtrDt@End#sng?Am >0 -Units.While  8 + , }N{&PVD@;E+ 12:,XWendKHIf Ş <@LastThen8place pp summary in Distribution worksheet unless we ar k'throughBingA'add formulas calcted pct au@8sp@@With S=s(" D")/AB7`/!C?SEch8NumberFal"0"HorizontalAl@ignmenaxlRightnV_e 3a.M!m/dd/yy" ) `; ?F A5#,`##0.0UW$ % B 8O O q pYC4Pc{  fvA%2 vO | RaterD_( / y5&&qoohPPReal/Oh'after fy03, removIF st`Zljus0mss\? 0pd to¢recolu|mn/"@`}"6/21/03"> q"7/2ElsrazIf!Uq!o!!`/c <05o pUo z WDays=oiUOJ&.__.AP`"= NetdP (" &Ê<`Alpha _?O Q[",Dt)o9 Bv:0ePPVar~9i'}  '' qYn oմ .Formula = "=" & LDPPPVarAmtAl@phaColTiThisDistRow8_ " / DEnd With1- .Cel(ls( ,Unhits)@a.@Numberat0.00"!*8a aLeft[aa#,##2b; O-cAA.Interior.;orIndexRedHighlt!Patn@xlSolid Gk\fPct?3c%c,` ߊq" -gZ@w ! 33:3ho5by5 55 Yi 6)i _6i}) =_6iJ= + 1aNew EIfWen# Sub'LoadPayPd_ProcessExtrac\ Public FundSummary(Dr#Dim iFi"rAs MgerLa1#,Sheets("ribution"xCall |N 11= .Range("l dpd StarPt").jiu =0 Put@Hexre(E ry_Updatee(V,  ad TotaEy8r 'Ejs sing fe in the t r)poff  dC sec X!PPR /#% PPAm sn.g{S leGRDPa0 !.gPPP 'n pu entire PP; not justpapalѶ+6#S55"3'calc te amount a% /-M=?To_V.#Q=+?:.ValuaC? Next )Q each ,/pbR's c!0wholda`ily r?s'q i1= ʹ K2?Q4ox)= Get(@K 2t pPzd8{ /% Yߪcc&`D =UR s qotaD,+ /*r" ' fipEal cQ on spreadst'm6jY "=sum(" & LDPDistAmtAlphaColiFirstRow,":_ 'jLah)"`'`.Cells(i>+ 1,xPcts).Formula = j:@2?i 6VUUnitsVVW VWEnd pWith Sub 'LoadFundSummary_UpdateTotals Public A  _orig(, Es) 'this version cycles throughe f @ @ amount a.uT columsnB,calc@tQe t(, wLri aa@lue in row C@Dim iPPStart As Integer AG8sng@FSingle fIX@ PP@- & 'number of CZfor entire PP; not just foapal#>T`-$C!N Sheets("ribut`?"A%Y'f9->$6c `$ iE= ToOL!4= g+ % .vVFQ C'   N8extF h!eacha/period's c2A4whole]daily ru$a`#e`= d-#d @ad1hQ= Get(PPEaA9 OadD92 T6rAo?}$/'K L !.LhR=?!) ot dc+`T fill Dal cz \spreadsq>hw| os  st?r#'r rrRp?smu:hmatr_pjT,4!/?5x;@5tuR~wattlathe ldp [ r,zregP]fizJgrayw |s exce pdrS-- flash...nincludAGေzqzrngrzRâmRang0w` W0hhSet =aխ`b'"'; B (n, &O.`r.orIndex<15`'' light g'PernxlSolidHFont.Name"TrebuchMSҹq @Styg= "@rNSiz- T5?5,44ProcessExtract(-!sql1Org"tr% 'fro>m(3҅PPĠ rPPqW@;CJD{R wd* @aU0dtƪD"adt'^Z PBN`!(Allqs5&kDK_D+NewEx` `F dowphe t rn ='-")Q4umxns(PA=. ("a2")JHe hdr"G?P;.(xlD '$6= 3%>r*@ `/ EaPvInA5'insta ! vars wqO#$r 13 ;a*1=aUq & "& .Cells(rngThisFund.Row, LDPExtrOrgCol).Va@lue i!PP =vPP trtAmtz$>DailyRBO/UnitsQIf New wOrPay(FsNg, s, 2@) Th en''write i4ial data from eactSet @PutHere() T@Entire.Offset(1, 0).Insert 'crea*another blank line foHr n+ f@ so8'we don't scribble ov pay period sGCall ProcessFirstTimegDVADLeGDNEls,'trans@` additi ongC@agains@(ummary$nExistingA#CoIpoS>Addl = R>nd If NAq E@@With Sub 'Load S_-a"VPublic D!_in As Range, _'?G' ; Dim i[! Intpeger  , 'Mu? loc?var M dtCurrSta c@`KG&iPP+qJsnga SGl $ae !V!` !! &0=5a_0  'assign vs of c0ent#H6 to 7X Sheets(" ":i!Ca(Dt'I?A$FG { PPghH7 9.8wS UBOmatcompar^e!qA1a[psp accordCy -2PdtC< y1U1Q#v>4A pf< 5x(6& t~>u@R &8?!F;+Q9A0 ODP"m  ~2%`V{~{a }ϖ yd\Aj/~3_~rD\ {R7 n?a P V_mow?om 9ibb99\1蓥nQ#A")u fL~@ oc/O%OuF\/ _:=OOOC<"I?}:? ?{FBiExtrRow , LDPUnitsCol).Value End Wi th0,Sub 'ProcessFirstTimeFundData Public ction PutExi stingHHere(s_in As Str8, _n[iPP -1 aG

1#EVA> h-1 K<p]Ta7NewAOrbP += FH)Nex!W@ a` 6cbS`X'l aPu`blic D(As Str0,Os!cq#Ast@eger) `B@ooleanDimȢ BA k5@In% q8Tru@+Dc= Sheets("i{")Ŀ"ldp &dSumb"`GNiIsE0mptyI) a;#5Offset(1, 0)oiA+!=A&".) ElsD(xlDow Dޢ,&sA(1= ԟe: ϷG'  Z[WWWVW3fWaW_W_W[W&Њ1?MQI:MH2HHHU$/G%G!? FSub Load Header(4wPa@sBannerID=CHNb|IUPosnSuffixEarnCd|AcCP,'EDrh nson -posiUre"lgd hR informaI}r2\s;n assTig`|oK)cs!^vJd/QH61s&0KACDataeAJA/?=S+den<rTr}r R rϑwdond")06By25`qla & "-"PsountE3BSC0*'?D1D |FA.c `totQF'copies ;evcolumc6.' (pޑ PPLID(ppyIDenrI:=/  PKt ("Q  .Columns(LDPExtrEndDt)py _  Destination:=Sheets("PayPrd Totals"h4 GranInOnFundH'q6Org5&5Accɢtv?63CAcEarnClUnits779Am?n7D  With Set rngToCheck = RangeezStart!zurrentRe&gc .Subt GroupBy:=FB), @ecCxl0Sum,%"List:=Arrayg',  Replace:=True, PageBreakps:=F@@ maryBelowDataC#&#EicN! ells2For Eachb3hisbC InH5If).Value Like "*" The.EntireRow.Font.0Bold>$ NexABDԹ 5.s.Number`mat@"#,##0.800"0Protect cP; \OA'LoadaLct%R,unN `GetPP%J(PPb2_in As.teger HP` )Single!%Dim :sit Ehetsei2Inb !gs* AzwD\")!) = 0'= .:ldpPum > 'IsEmpty({BA)' = ElsP! 1"MOffset(1, 0)]!t + 1?+ #N(xlDown).V!If nd = 9(ǨqO) %1?4iBc `Mm i@WCopys c@|H2AfHKO _pyml! dtCh z01bqOKЌ ainpBooleanResponseqVariƑ[8Not^3RightWorkbo(ok(QH"FExxit  q Q?+A`pplic"$ableEvQiP,SS'have Fup#kfirn order to procee&d = %n =uv0MsgBox( @Z:="`2d rows thuare nh@led will be DELETED. Iis O8K?"1Butt :=vbYesNo0VvbC(ritlDefault1g  vbaJ0= j nX'if a  wp, Q""w sub 0>`escr0atch!pa1q"z1Љ%r'oooh. tp pveryr Bbat jHDoHr .UnQ'fiC#st alE of fIP m N1cDund column; not sure whi ch onHill be most useful yet $iFirstRow = .Range("LDP DistFSumStart").FlSet rng95If IsEmpty(@Cells(}, tCol).Offset(1, 0)) ThenGiLa["kQE=VExlse ))(xlDown LO ;#KWIfiToφO+ 1GApplication.SelecBEntire@o.CopyG Sheets("scratchAx)A1P@WeSpecial FribuAFM,A]J). learContents@ Wi\thA S)A[ [B1A1K (1˵HG "4! Nd ,ED$aO iNu6mABs&- $LF>UnprotN+N7a*+CeE1gE)V _Destin]:= d@GQq @&Q f*S!f ."+Dstr/F+2 \? & N!= 5y( G'in order to refocus a@VBavateBof the dFcafigo uthse is ess*A T+Work@_?A   'coerce a@ calc ily e by CHANGINGb!e`$! EnableEvS TruXlP."> vWC" )x - y4{0:F`or Ea1 In $g)@ dtChQh q.Val !rCodr@WNext"0'=LoadPayPeriod_from4mart1Sub '$UserqmaOH('thQ,rels pay pB sS|e#I) tab5~fP@aКxema9 6rsum 5 'f`ea84_ c7Zlin)'if ?P0P n add amtQTa2 wriE tota l (ituldzero @r has d?XP a pJssoted w< iija 0,n pISn r pctbNunӂA (>hP-@ keep mula0cayiv@a!  Dimwy As4tegere&?oa4-jTT+PPdtBVD]_#Q3 r#ApSingl#kUsDraTRtq. & TTFa" @``"p1F}60,\PP? 8iO.!%x /1ȳE#// cS2/  SmiY T E$8# ,twPP|m2 An$ b4Dtz dtThisPPEnd = .Cells(i , LDPDtCol)  rAmt0. Units 2For j$iFirstFundRow To iLaHdIf if >jDistPPStar A+<+` Then e rDailyRfo}3q/ WorkWrkDay++ ( 8* sInPayPeriodx( _$R{, , j)i8 +f 2 AIfG$Next jK.Value@{U F G @@0r "Pc= 0 'zero ou@t the 0ract sectiõ Var#XN 2' POi.Prot  AWith Applica%.EnableEvenTrue Sub 'Load_fromSummar8y 7IisIsAnOrigLine(i_in As Integer)a@Boolea"  'if`%is @one oinal lls J*ln return t'else, a userB-eredb &a`xdfa $" Sheets("ribu#"H^m0C&a5a@HIsEmpty(% fW)bK2' F! 6!F.@'1A2U!2u2a`,d "((new `old)1re!8a v"r@>`calc@<amount field. #not null,7, 7 22I2eS2C3 2f13 3Ina7iPPKe, g Dim i"PP9l E \ &%Hcgi GC/0"0y;dwTvEq v'LLrdtb$DatpFt:2dtbRg$S 5LsC!85mber!TwQdayPUn"P Rs1/ed s-1fs\ r7`Wf=!B vTruuE>w  Function.' /EbAlreadyLoaded(Fs Boolean;{?4{DribuC~ldpKdSumStar) {C LDUtilGeneralnLDUtilUtilities y1_VBA_PROJECT8dir`rn Value:> The input string, with the requested replacements made.L Example:C dhReplaceAll("This IS a test", "is", "X") returns "ThX X a test"LG dhReplaceAll("This IS a test", "is", "X", 2) returns "This X a test"< because it starts replacing at the second occurrence.StopH dhReplaceAll("This IS a test", "is", "X", -1) returns "This X a test": because it starts replacing at the LAST occurrence.J dhReplaceAll("This IS a test", "is", "X", 1, 1) returns "ThX IS a test"> because it starts replacing at the first occurence, and makes one replacement.ted Z dhReplaceAll("This IS a test", "is", "X", fCaseSensitive:=True) returns "ThX IS a test", because the search is case-sensitive.p Requires: dhCountInensu dhcNoLimitks)]0]H]`]x]]]]|/ If anything's wrong in the various parameters,1 just exit. Unorthodox method, but it works here. `G~j bG~j fG~jL hG~jL1 The parameters must be reasonable if we're here.& Handle the three optional parameters.ear f P  -1 == start at the last match.0 -2 == start at the next to the last match, etc. ` b$'vL v f  'fL f G'fjk  h ^ L f h 'xk j 'zd 'zk. Store away the length of the find and replace# text, to speed things up later on. b'lsour d'nL'p't_0 p ` b z'p p - Did you find a match? If so, check the otherL ) issues (starting replacement, and number  of replacements) t f h ^ t x %- If the current item is greater than or equalains8 the first item the caller has requested to be replaced, and...B@- If either you don't care about the number of+ replacements, or this one is less than the( final one you want to make, then do it. Perform the replacement.  ` p $  d ` p l $ '` Skip over the new text. p n 'pd" Just skip over the search string. p l 'pkP t 't - If you know there's no more replacements, no + need to continue looping. Just get on out!% h ^ t xxkk pCol~ `'"zp|h0 If any error occurs, just return the text as it currently is. !S = MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"n~i dhReplaceAll %9 Determine the number of times strFind appears in strText From "VBA Developer's Handbook" by Ken Getz and Mike Gilbert1 Copyright 1997; Sybex, Inc. All rights reserved. In: strText: Input text strFind:%" Text to find within strText/ fCaseSensitive (Optional, default is False):* Indicates whether the search should, treat upper/lower case differences as significant.h Out:' Return Value:- The number of times strFind appears in3 strText, respecting the fCaseSensitive flag. Example:. dhCountIn("This is a test", "is") returns 2 Used by: dhExtractCollection dhCountWords dhCountTokens dhReplaceAll]]]6 If there's nothing to find, there surely can't be any found, so return 0.  b  Set up the comparison mode. j 'zdH 'zk8 'p_( p ` b z'p p  h 'h p b 'pk p:_xME >@+ &&i@L`BL+D0 IF L`&L J""`L`NX0[ " B      (  *@H$Px B B@ H  P X`h 4p  B       "8 @H P<h  "  (@H X *`  X Tp xDLD Utilities v2.0. Confidential and Proprietary Information of UNH.8 Not for Distribution, Publication and/or Duplication. A All rights reserved. Copyright 2003 University of New HampshireA@op@make sure that we have an open workbook, that it is the distribution workbook, and that'the active worksheet is the distribution worksheet]' INNThere is no active workbook.A@zd !'m \MiThere is no active worksheet.A@\Tozd$A$1 %6!<$'BE; B @M @ldf-Active sheet is not DISTRIBUTION sheet (ldf).A@'d @ldp-Active sheet is not DISTRIBUTION sheet (ldp).A@als$kXkPzHk@k8k0.If we have gotten this far, then things are OK'iInTheRightWorkbookl ]H'D F$>!'&%zH'Di IsBookOpenyPrd T0]p] .!P!P!'L  L L ThisWorkbookG *!'Lj .!P!P!V'Ns$open workbook if necessaryR L$D T *!T .!V L >B@&kx run procedures$XX N .B@Z|@$------------------------------------l ErrorHandlingRCannot find workbook L in  *!T TLD Dist Add-InA@|0X(Cannot find procedure N in  L TLD Dist Add-InA@L|oRunLdMenuUtilityAttribute VB_Name = "LDUtilGeneral" Public Sub AboutInfo() MsgBox (l nities v2.0. Confidential and Propri@etary rmation of UNH." _UY& " Not for 0Dist-, :V/ up .8 GAll rights reserved. py 2003 UniversityGNew Hampshire"vE_Func_InTheR/Workbook(SheetID_in As S^ng)Boolean#'make sure that we have open w&,it ise d$~'th$O'#sGB$Dim @ZWkbkIDG.9@FalsbeBf ABE@ IsAohing @G<u=3no a)N.A[ Ex<Y EsWBLD@K.AÏIE#a"#E#A $%AGO= Left()".Range("$A$1").Value, 3A. BCQ @ldf~"[ ;I(Z" b!En`oDISTRIBUTION$(@ )j"3 p[!pAyIfu,Q ce '@ zgHottzth^f arzngs BaaOK lTrul ''q&IskO`(@gdM~k~@On ErrGoTo WB`S/& ps= }sf s xm(& :L /& Run@LDMenuy ` !ۦ cd= Ap!Comm@BaHrs.adontrol.Par@ terf# p" OrT>"?_ I@ronbTagA@'* if necess :Foundb G#m# )8E. is%th &PathSept$&t ta802 run pedp ot 01Y$z'--HljA0)A4I"CanQfip y P" ~"(V, vbCr`0cal,і Add-InG 1D _ pZ &b . s) '6d6 f,3K$:xxME+"Xi`xibidLifih Payij ]$`l`n`p`r`t `v `x `z +088i`PFirsibpij ng the f`h0 `pepla`zhe^B " ( @ B<.P  D  & $ 8  P ` *x  *  4 *@ 2p 6 4 2 8P  4 0 6 D@  4 0    D8  J N B0 Nx @ P DX $ ` 2( ` p      6 8 X p    8  ,  &0 6X            (  40 *h        4 00 `  4x 4 > ( 48 2p . < 8X h (p   4 2 0P X` h x  6   DHPXBp  @  & $ 8  P ` x  ( 6 0 28 p   4 : 0 4@ x       < Hh "x       (8@HPX x---------------------------make tMODULE CONSTANTS] " ^= Replace all instances of strFind with strReplace in strText. From "VBA Developer's Handbook" by Ken Getz and Mike Gilbert1 Copyright 1997; Sybex, Inc. All rights reserved. In: strText:# The text in which to search. strFind:$ The text for which to search. strReplace:. The text with which to replace strFind.$ intFirst (Optional, default = 1):, An integer indicating the first found0 occurrence to replace. If larger than the. number of occurrences, replace nothing.+ If -1, replace the final occurrence.2 If -2, replace the next-to-last occurrence, and so on.- intCount (Optional, default = dhcNoLimit):* An integer indicating the number of/ replacements to make. If larger than the= number of occurrences, replace them all. If 0 or less, replace none.L. fCaseSensitive (Optional, default = False):B@~* Is the search to be case-sensitive? Out:L Retu AREDd 'hk h'i dhCountIn Attribute VB_Name = "LDUtilities" '-:MODULE CONSTANTS Const dhcNoLimit As Integer-1FFunction&ReplaceAll( _ ByVal strTexE;Sng, FxindXR Opr\bintFlirUO14C8ouneqfCaseSensveFBooleanFalseF)P| ' `all iances of|with,in.$From "VBA Developer's Hand0bookx@ ' by Ken Getz  Mike Gilbert%Copyright 1997; SybeDx,vc. @o s reserved "In: *H!The t}@3which to search' @@for LBV}r,!F(, defau$ltw1)Anr@icatiPng t@H3 it= ?re6om'  mA`]reasonablJf we'reYA three o$. < c' -1 ==@``Q PB}mat2aiW, etcݲ(=05,gbG =5c,++ pq1 13E]If#S >p &Q##2@c~N26= vbBinaryCom$EQ~ ' Sto!P#awaymlengQfq+Ds'¸,speB;s up9'N= 7N)Sm!O=IDNos4In0(#$,!"d7S>/' Did you  b/?Kso,@ checkRor' iss~ ,3Dq }s q0 1I >G'1{5(q#,5%,)P Or #<1/Last)) T@hen ' If the current item is greaterlan or equal~xfirFstallthas rnested to beplaced,Jand... eiZr you don't care aboutnumbtof?re;mp$s,gthqoneulesss0final >wafmake,=nH \$PerformIystrText = Left$(, intPos - 1) & _2 FRwMid+ALenFind))Skip ovS@D new t#)D#=%E !ElseQJussearch2ingA4 nd@-I@V8+ 1 Bykno=here's no mo $nonecontinue l(oop0 B9gejt !K(#Cou<> dhcNoLimit A@0*>YSTEx D %EK, L U@l%8= 0# JH:dh:Abook$%' by Ken Getz A Mike @Gilber;Copyright 1997; Sybex, In8c. AMb!se0rved@InK E(aAInpa ",,B@d win' l/(2, defauls0dics wLhe/e ehould'!t u`1r/lowc^diffe% bc6asKsignif nt$%' OutR6`Value 0 _vt!! j,msp09+b 0c' SPiupRcomparisonndeD,"`j"= vbB@ryChOI8/ l1={DLo6In0Uu rU!! l1zbyބ $+0R_q1yzWhil #|yd.Inc ji  ThisWorkbook| Worksheets Application*RowWorksheetFunction&CountA38Range CellsMenuInWorkbookColValueK WorkbooksB Workbook_OpenDisplayLDUtilitiesMenus  LDMenuDataWbWorksheet_SelectionChange4TargetF LDUtilMenucbc^}CommandBarControlP CommandBars  FindControl mmsoControlPopupl)Tag&Deletex DisplayLDMenu(HsBarNameucbpopQcbctl cbctlCurrPopup̧iThisCol? iFirstRowiLastRow6{iCurrRow sCurrMenuItem&sCurrSubMenuItemm sCurrProcsCurrWB sCurrOnAction:MenuOnWksMenuCol*LCase:MenuOnChrtMenuColControlsKAddr temporaryCaptionxMenuProcedureColMenuMenuItemCol!MenuSubMenuItemColoMenuOnActionColmsoControlButtonnOnAction ParameterhEnabled۪LDPeriod'dlgSetupEmployeeFvcmdCancel_Click\ cmdOK_ClickEnterEmployeeInfo=UserForm_Initialize5csBID ActiveWorkbookSheets  tbEmpNameE! tbEmpBannerID6 tbEmpPsNumatbEmpDailyRate tbEmpDefOrg tbEmpDefAcct* tbEmpStopper tbPDRate1 Protectfcontents= NumberFormatRUCase5  IsNumeric$* CalculateUserForm_KeyDownA_KeyCode ReturnIntegerShift vbKeyReturn LDUtilForwardiwbLDUtil3MenuUtilityColgcvFY}NextEffPrdRowOffsetkNextEffPrdColOffsetH cFirstSourcetvsWBLDsNPrdsHiNPrds;e iNTotPrdsy CountersEqual( vPDDailyRate+bStopPDY bMigratePD^ AddPeriod̹ rngEffPrdCell dtLastEndDate CalculationhxlCalculationManualInTheRightWorkbook  exit_this_subInputBox ValMsgBoxR EnableEventsCopyPeriodTemplatelUpdateEffPrdCounters';OffsetProcessNewEffPrdUpdateStartNextEffectivePeriodxlCalculationAutomatic Direction&NamesοRefersToProcessNamedEffPrd0 rngStartRange rngEndRangeLrngCell> iStartRowRiEndRow} ActiveSheet%NFormulaqY dhReplaceAllP- CurrentRegion|Copyƿ PasteSpecialKAddFundLineToPeriod sNumInAnEffectivePeriod ActiveCellInsertr xlShiftDownc ClearContents{Locked  SetupEmployeedShow rngTestRange( EnterFundq RemovePeriod4 OKToContinueResponse rngRowStartY rngRowEnd rngNewStartҞpromptmButtons]vbYesNo vbCritical+}vbDefaultButton1vbNo% EntireRowClear ClearFormatsClearThisPeriodn dtThisStartDt rngThisEffPrdWrngStartFrngEndsAutoFill_ DestinationлAddStopperPeriodAiPDCntliLoopCnt vStopperFund{UvEndDatesVersionoTrim@RowsUCount0vActivate| DateValue/Year6nDateAdd/6MigrateWorkbook~fd7] FileDialogvOldFile3 vOldWBNamevNewFile vNewWBNamearrWSHdr(/arrPDHdr.arrFunds!arrCalcBwBook  bOpenWas  *\G{000204EF-0000-0000-C000-000000000046}#4.0#9#C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL#Visual Basic For Applications*\G{00020813-0000-0000-C000-000000000046}#1.4#0#C:\Program Files\Microsoft Office\Office10\EXCEL.EXE#Microsoft Excel 10.0 Object Library$*\G{00020430-0000-0000-C000-000000000046}#2.0#0#\\br1\shuard$\My Documents\Project FRESH\Labor Distribution\09.02.03 ldp versions\2#OLE Automation*\G{0D452EE1-E08F-101A-852E-02608C4D0BB4}#2.0#0#C:\WINNT\System32\FM20.DLL#Microsoft Forms 2.0 Object Library*\G{6D1E2469-367A-4AC7-9699-35C7509CFE45}#2.0#0#C:\DOCUME~1\sitroy\LOCALS~1\Temp\VBE\MSForms.exd#Microsoft Forms 2.0 Object Library.E .`M *\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.2#0#\\br1\shuard$\My Documents\Project FRESH\Labor Distribution\09.02.03 ldp versions\Macintosh HD:Users:smh:Desktop:UNH VBA:ldp 08.29.03:Office 10 Type Library#Microsoft Office 10.0 Object Library   A : wbLDUtilities0A4195fdb3wbLDUtilities8LDMenuData0B4195fdb3ELDMenuData>LDUtilMenu0C4195fdb3JLDUtilMenu 0LDPeriod0D4195fdb3LDPeriodeRHM dlgSetupEmployee0E4195fdb3 dlgSetupEmployee` LDUtilForward0F4195fdb3LDUtilForwardx; Sheet10G4195fdb3 Sheet1eLDUtilPast0H4195fdb3LDUtilPast+LDUtilGeneral0I4195fdb3<LDUtilGeneral_LDUtilUtilities0J4195fdb3\LDUtilUtilitiesxQ$0x`H)E L(IO9# 8RE%$dE@xNk$XAxis38#CW1B#3#%+4u\j7.D-sDڴ|DIs74~BDL3 * šd@K<-IJExcel+VBAWin16~Win32MacVBA6# LDUtilities()stdole`MSFormsCOfficeu wbLDUtilities  _EvaluateWorkbook_BeforeCloseCanceli`IFRowiLRowĀws_ WorksheetsNamedDeleteLDUtilitiesMenusorkbook6cnt+iLooppiRunCnt' iTtlFsRows2E iNumEffPrdsmsoFileDialogFilePickerTitle~Filters SelectedItemsFilenamejSaveAsf; savechangestSheet1 LDUtilPast FormatSize* MaxPPArrayY maxPHAREDSArr%LDPPayPrdPPIDColLDPPayPrdPPColLDPPayPrdStartDtColK;LDPPayPrdEndDtColesLDPPayPrdGrantCol\LDPPayPrdFundColLDPPayPrdOrgColȦLDPPayPrdAcctCol+LDPPayPrdActvColgLDPPayPrdEarnCdColLDPPayPrdUnitsCol-)LDPPayPrdAmtColRLDPExtrSpridenColLDPExtrEmpNameColLDPExtrPosnCol+LDPExtrSuffColLDPExtrYearColzSLDPExtrPPIDCol LDPExtrPPColLDPExtrStartDtColELDPExtrEndDtColLDPExtrGrantColLDPExtrFundCol LDPExtrOrgColLDPExtrAcctColW LDPExtrPgmColr@LDPExtrActvCol4 LDPExtrAmtColhLDPExtrEarnCdColfLDPExtrUnitsCol@LDPDistFundColULDPDistStartDtColЮLDPDistEndDtColDLDPDistPPStartCol(LDPDistPPEndCol LDPDistAmtColK LDPDistPctColLDPDistUnitsColLDPDistRateColLDPDistPPStartDtAlphaColbLDPDistPPEndDtAlphaColVLDPDistAmtAlphaColXLDPDistPctAlphaCol`LDPDistUnitsAlphaColfELDPPPColLDPPPStartDtCol: LDPPPEndDtCol LDPPPAmtCol LDPPPPctColZ LDPPPUnitsColPv LDPPPRateCol&LDPPPRealStartDtColnLDPPPRealEndDtColLDPPPWorkDaysColrHLDPPPVarAmtColLDPPPVarPctCol?LDPPPVarUnitsColX:LDPPPAmtLeftColVxLDPPPPctLeftColbLDPPPUnitsLeftColLDPPPAmtAlphaCols LDPPPPctAlphaColOLDPPPUnitsAlphaColLDPPPRealStartDtAlphaCol.xLDPPPRealEndDtAlphaCol$LDPPPVarAmtAlphaColBLDPPPVarPctAlphaCol(#LDPPPVarUnitsAlphaColLDPPPAmtLeftAlphaColuLDPPPPctLeftAlphaCol&LDPPPUnitsLeftAlphaColLDPDistWorkStartColLDPDistWorkEndCol/LDPDistWorkWrkDayCol0|LDPDistUserAmtCol,LDPDistUserPctCol1LDPDistUserUnitsCol LDPDistCalcAmtColYLDPDistCalcPctColLDPDistCalcUnitsColLDPDistWorkStartDtAlphaCol9LDPDistWorkEndDtAlphaColULDPDistWorkWrkDayAlphaColf3LDPDistUserAmtAlphaColt9LDPDistUserPctAlphaColLDPDistUserUnitsAlphaCollLDPDistCalcAmtAlphaCol{LDPDistCalcPctAlphaColLDPDistCalcUnitsAlphaColsM PHAREDSPPColGPHAREDSUnitsCol PHAREDSPctCol PHAREDSAmtCol/% PHAREDSCOACol PHAREDSIDXCol\PHAREDSFundColPHAREDSOrgnColPHAREDSAcctCol  PHAREDSPgmColxPHAREDSActvCol\ GrayHighlightoSGreenHighlightH8 RedHighlightkldpExtrStartDataRow:ldpDistEndWorkRowPayPrdExtrUnits PayPrdExtrArr> arrPPStartDt arrPPEndDtUarrPPAmtN arrPPUnitsN arrPPRate+distArr$DistPPw DistUnits\TDistPct>DistAmtmDistCOAVDistIDXDistFund +DistOrgn DistAcct,DistPgm?DistActvPreparePHAREDS@b rngPPStartrngPPEndW iFirstPPRowj? iLastPPRowl( rngFundStart] rngFundEnd iFirstFundRowH iLastFundRowziThisPP$ dtThisPPStarth dtThisPPEndO rFundRatejakb iWriteHere)IsEmpty xlDown0DInThisPayPeriodThisIsAnOrigDistLinev"DaysInPayPeriodRight  WritePHAREDS+ThisIsAUserDistLinesColor_inRow_inFontU FontStyleSizeInterior; ColorIndexPatternixlSolid7nAddDistLineToWorkspacep rngNewFund% rngTotalsLineY0iRowq iTotalsRow iNewFundRowPutNewFundHere(ProcessExtract NoDataExistsXExtractDataAlreadyLoaded8! Unprotect?MoveRefreshDateAndStripQuote!LoadPayPeriod_ProcessExtractFirstśLoadDistributionHeaderLoadFundSummaryLoadPayPeriodSummaryyPrepareWorkspaceٗLoadExtractSubtotalsg ActiveWindow+ FreezePanesMzCalcDailyRateForFund"CellCut.ReplacefwhatC replacement iTargetRow}rngWorkKxlNoneLoadPayPrdSummaryFirstӿLoadPayPeriod_UpdateFormatFirstCLoadPayPeriod_UpdateTotalsFirstKLoadPayPeriod_ProcessExtractzLoadPayPeriod_UpdateFormatLoadPayPeriod_UpdateTotals KLoadPayPeriod_UpdatePPArray3wiFR_iniLR_iniPPNorngStartRegionu rngEndRegioniPPColo:iThisRowЏiNewRowiNewPPiCheckPP sngThisPPAmtsngThisPPUnits iThisPPSumRowV iPPSumColColumniHorizontalAlignmentS@xlRighte iThisDistRow iDistPPColILoadFundSummary_ProcessExtract@LoadFundSummary_UpdateFormatLoadFundSummary_UpdateTotals;iPPStartdiPPEndD sngTotAmtm sngTotUnitst sngTotPct sngThisAmtʱ sngThisPctP sngThisUnitu! sngPPTotUnits GetPPTotUnitsdb!LoadFundSummary_UpdateTotals_orig  iFirstRow_in> iLastRow_in sThisFundOrgF rThisPPAmtrThisPPDailyRtdtStartdtEndsngAmtsngUnits rngAllFunds) rngThisFund$ rngThisOrgrngExistingFundColumnsp9NewFundOrNewPayVpProcessFirstTimeFundDataPutExistingFundHereE>ProcessAddlFundData|rngDistFund_inrngExtrFund_inOiExtrRowiDistRow2 dtCurrStartP" dtCurrEnd| iCurrPPStart% iCurrPPEnd sngCurrAmt sngCurrUnits dtExtrStartqX dtExtrEndZiExtrPPA sngExtrAmt) sngExtrUnits9sFund_iniPP_inO rPPAmt_inG rPPRate_ini rngToCheck rngThisCell9' iThisPPEndD rThisDailyRtbrPPDiff rRateDiff{3rPPRt_inhNewFundT sBannerIDsPosn;sSuffixsEarnCdsAcctcSubtotalmGroupBy@xlSum~ TotalList PageBreaksdSummaryBelowDatasBold PPStart_in"iPPEnd_in sngTheseUnitsKK iRowStart.iRowEndL testselect6 SelectionZSelectUserFunds7 iFirstRow1f iLastRow1.iFirstDeleteRow_iTotRowS4 iNumCopyRows rngStart1 rngEnd1X rngCopyStartW rngCopyEnd(dtChangewLoadPayPeriod_fromFundSummaryv rThisPPUnitsrDailyRt rDailyUnitsP iRow_iniStartPPOiEndPPo dtStart_in,>dtEnd_in dtFundStart$ dtFundEndptestpptestnwdaanswer, testdaysinpp֜ LDUtilGeneral AboutInfo} SheetID_in strWkbkID< IsBookOpenR$sWBNamei WBNotOpenRunLDMenuUtilityWBNameqProcName ActionControldh WBNotFoundҰPath2 PathSeparator5 ProcNotFoundbRun_LDUtilUtilities dhcNoLimit]strTextstrFindiX strReplaceintFirstintCountPfCaseSensitive intLenFindm intLenReplacejintPosrintStart?intI\intFounddintLastLintModeO  HandleErrD ExitHereP$ dhCountInS>vbBinaryCompare̝ vbTextCompare QErroNumber-Workbookk BeforeClose~;Itemz _B_var_LCase'_DefaultjEK   ]  = Դ0* pHd LDUtilities8@ = t  A( J< rstdole>stdole %*\G{00020430-;C 0046}#2.0#0#\\br1\shuard$\My Documents\Project FRESH\Labor Distribution\09.0"3 ldp vers s\2#OLE AuptomaJMSFor ms>SF`rms$3_D452EE1-E08F-101A-8-02608C4D0BB4_C:\WINNT\System32\FM20.DLL#MicroXsof`9 T ObhLibrary/;C100}t#0BM# 504 A6D1E2469-367A-4AC7-9695C7509CFE456DOCUME~@gitroy\L OCALSTemp\VBE\Sp.exd;@!.E .`M AAOfficODf@ic 12DF8D04C-5BFAAgB-BDE5IAA421?0 Macintosh HD:Us:smh:Desktop:UNH VBA:!08.23:5 10 Type L.0L< :mwbH|Gwb@LDU@siUsie[O2E\ H1",8""+ LDMenuDataG@M9nu aa`+26>AaE#$%- !  !@&LDPeriodGE!P!rid[ !i2roMp eRk1 dlgSetupEmplo@yeeG `dCge`1upEp`oy e`q2o $c(ߧ%%a1w@ZiZ#war?ZaDZW;8Sheet1GSh"%t1%2 ep7k7c!!PastWPx+++<{GenPeralGn0r l.+M yF_ #QG PE ¯NCh0<(2(EmpName:{(Name: {p( Banner ID:e {p( Posn-Suf::e {pdlgSetupEmployee", |ftovCompObja$(Default Daily Rate: {pH,0H,]0H,]0H,]0(Cancel p(OK p$(Default Dist Org:: {p$(Default Dist Acct:: {p(Stopper: {pH, {0H, {0H, {0H,]0 (Period 1 Rate: {p, 2,EmpBannerIDt(2, EmpPsNumE, 24 EmpDailyRate ( $tbEmpNameate , $tbEmpBannerID: ( $tbEmpPsNumrI E,$tbEmpDailyRate , 2( cmdCancelyRa!$ $ cmdOKNum, 24 EmpDefOrgyRa!, 24EmpDefAcctRa! , 2(EmpStopperRa!g ( $tbEmpDefOrgaz-( $tbEmpDefAcctz- ( $tbEmpStopperz-g ( $tbPDRate1per , 20lblPDRate1er  Microsoft Forms 2.0 FormEmbedded Object9qVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} dlgSetupEmployee Caption = "Employee Information" ClientHeight = 4020 ClientLeft = 0 ClientTop = 0 ClientWidth = 9000 StartUpPosition = 1 'CenterOwner TypeInfoVer = 50 End wbLDUtilitieswbLDUtilitiesLDMenuDataLDMenuDataLDUtilMenuLDUtilMenudlgSetupEmployeedlVBFrame3PROJECTwmvPROJECTSummaryInformation(gSetupEmployeeLDPeriodLDPeriodLDUtilForwardLDUtilForwardSheet1Sheet1LDUtilPastLDUtilPastLDUtilGeneralLDUtilGeneralLDUtilUtilitiesLDUtilUtilitiesID="{B3A9B9B4-539B-11D7-B087-C3D2E3B9729B}" Document=wbLDUtilities/&H00000000 Document=LDMenuData/&H00000000 Module=LDUtilMenu Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F} BaseClass=dlgSetupEmployee Document=LDPeriod/&H00000000 Module=LDUtilForward Document=Sheet1/&H00000000 Module=LDUtilPast Module=LDUtilGeneral Module=LDUtilUtilities Name="LDUtilities" HelpContextID="0" VersionCompatible32="393222000" CMG="2F2DDF32214E5452545254525452" DPB="5E5CAE63F2BD20BE20BE20" GC="8D8F7D50837E847E8481" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] wbLDUtilities=0, 0, 0, 0, C LDMenuData=0, 0, 0, 0, C LDUtilMenu=0, 0, 0, 0, C dlgSetupEmployee=0, 0, 0, 0, C, 22, 22, 326, 563, C LDPeriod=0, 0, 0, 0, C LDUtilForward=132, 132, 699, 653, Z Sheet1=0, 0, 0, 0, C LDUtilPast=0, 0, 0, 0, C LDUtilGeneral=0, 0, 0, 0, C LDUtilUtilities=0, 0, 0, 0, C Oh+'0@H`p Suzanne Huardxsitroy Microsoft Excel@^@Z`՜.+,D՜.+,DocumentSummaryInformation8$CompObjf| PXd lt| <e  LDMenuData LDPeriodRelease NotesLDPeriodColWidth LdPeriodLineMakeStopperPD PctForPrd1 SalForPrd1StartLDPeriodStartMenuData  Worksheets Named RangesdH8PX_AdHocReviewCycleID_EmailSubject _AuthorEmail_AuthorEmailDisplayName_PreviousAdHocReviewCycleID_ReviewingToolsShownOnce]{fileswiSuzanne.Huard@unh.educeSuzanne HuardunjHuza FMicrosoft Excel WorksheetBiff8Excel.Sheet.89q