Would not let me upload the file but I can send it to you if this seems like something you can do easily. my email is [email protected] if you have any more questions. This is do very soon and I need some serious help, I much appreciate it!!
The following are coding assignments. Modules are provided to write
all necessary code (mdlPart1, mdlPart2, and mdlPart3). Please perform the tasks below,
write the code to the best of your ability, including comments as you see fit.
Part 1 (5 Points):
1) You receive data in a file that contains dates in the following format:
YYYYMMDD Sample: 20100101
a) Write a function in mdlPart1 that will convert the date to
date format: MM/DD/YYYY
b) Call this function "DateConverter", with one pass-through variable called
"dtYYYYMMDD".
Sample Data Results:
20010317
20120809
20130430
c) Fill in range C18 through C20 with the function to display results
of converting range B18 through B20.
Part 2 (5 Points):
2) Write a process that uses For… Next loops and an array to fill range B3:D10
of the Part2 worksheet. Write and save your code in mdlPart2.
a) To create the array, use the following code:
Public strCellRefs(1 to 8, 1 to 3) As String
Note: This is a Public variable, so it goes outside the subroutine code.
b) Fill the array with the following text:
Row: (row reference); Column: (column reference)
strCellRefs(1,1) would contain the text: "Row: 3; Column: 2".
strCellRefs(8,3) would contain the text: "Row: 10; Column: 4".
Again, fill this array using For… Next loops.
c) Using a single command, paste the entire array into the cells B3:D10 on the
worksheet "Part2"
Part 3 (10 Points):
3) Write code in VBA that does the following with data on the Part3 tab:
Code for this project is to be stored in mdlPart3.
a) Creates a variable called "lngLastRow", and stores the row number of the
last row of data in column A of the Part3 worksheet.
Use the command ".end(xlDown" or ".end(xlUp)" to accomplish this.
b) Creates a 12x12 array called "dblLagTriangle".
c) Adds the claims in Part3 to the correct location in the array from part b.
For instance $661.93 would be placed in the array position (1,3).
Following that, $762.00 would be added to that same array position.
d) Posts the final resulting array in I4:T15 of the Part3 tab.
e) Creates a messagebox to the user that states the macro has completed.
Part 2 Spread Sheet
Fill in these cells with the Cell Reference, listed as "Row: x; Column: y":
For example, range B13 would look like this:
Row: 13; Column: 2
Part 3 Spread Sheet
Claims data: Total: $59,097.11 Total Check: 1,423.91 Date_Start Date_End Month
Use this data to fill the lag triangle to the right. Paid Month 1/1/12 1/31/12 1
Claim_ID Date_Incurred Date_Paid Billed_Amt Incurred Month 1 2 3 4 5 6 7 8 9 10 11 12 2/1/12 2/29/12 2
835385 1/7/12 3/5/12 $661.93 1 1,423.91 3/1/12 3/31/12 3
329221 1/8/12 3/9/12 $762.00 2 4/1/12 4/30/12 4
366885 1/5/12 3/9/12 $888.73 3 5/1/12 5/31/12 5
348844 1/4/12 3/12/12 $543.73 4 6/1/12 6/30/12 6
884768 1/14/12 3/24/12 $745.97 5 7/1/12 7/31/12 7
115285 1/12/12 3/24/12 $1,040.52 6 8/1/12 8/31/12 8
994943 1/13/12 3/25/12 $788.38 7 9/1/12 9/30/12 9
266968 1/14/12 3/25/12 $110.24 8 10/1/12 10/31/12 10
585292 1/25/12 3/26/12 $86.26 9 11/1/12 11/30/12 11
943221 1/2/12 3/30/12 $978.90 10 12/1/12 12/31/12 12
305591 3/13/12 3/31/12 $1,151.37 11 1/1/13 13
513807 1/21/12 3/31/12 $908.78 12
438432 1/14/12 4/5/12 $524.70
141164 1/20/12 4/6/12 $635.83
735894 4/7/12 4/23/12 $909.87
935419 2/28/12 4/26/12 $875.79
989455 2/25/12 4/28/12 $307.11
984061 4/1/12 5/10/12 $627.74
817531 3/5/12 5/11/12 $697.05
666218 3/4/12 5/11/12 $192.74
319254 2/18/12 5/15/12 $602.46
473002 3/2/12 5/15/12 $772.57
372787 1/8/12 5/18/12 $495.57
654002 4/24/12 5/25/12 $600.73
747537 3/13/12 5/29/12 $809.51
219837 3/1/12 5/30/12 $237.09
120539 3/24/12 6/4/12 $458.87
231830 5/4/12 6/7/12 $303.38
639460 3/18/12 6/8/12 $582.25
658247 3/10/12 6/9/12 $694.27
923726 4/19/12 6/17/12 $1,192.03
268899 2/18/12 6/19/12 $298.75
183119 4/9/12 6/19/12 $356.36
140469 3/17/12 6/19/12 $706.91
263616 4/10/12 6/20/12 $639.01
470158 4/14/12 6/21/12 $871.42
517174 3/17/12 6/22/12 $926.14
584761 5/4/12 6/24/12 $737.25
719278 4/1/12 6/25/12 $1,012.70
795811 5/22/12 6/29/12 $1,118.03
673505 4/15/12 6/30/12 $498.90
732059 4/15/12 7/2/12 $1,017.17
776009 4/7/12 7/5/12 $482.79
164799 4/25/12 7/6/12 $826.66
787284 4/15/12 7/6/12 $286.72
708952 5/11/12 7/13/12 $95.99
656928 4/20/12 7/23/12 $636.86
897125 5/29/12 7/25/12 $349.87
426400 6/8/12 8/3/12 $1,150.61
692447 6/16/12 8/3/12 $286.60
319139 5/22/12 8/7/12 $815.74
214789 7/4/12 8/9/12 $673.29
303869 5/13/12 8/14/12 $989.33
867235 5/21/12 8/14/12 $557.85
698854 4/12/12 8/19/12 $440.14
590013 6/21/12 8/20/12 $855.45
758304 6/3/12 8/20/12 $1,068.33
944825 5/29/12 8/21/12 $633.68
578890 6/28/12 8/25/12 $492.80
639506 6/21/12 8/28/12 $374.96
655078 6/29/12 8/30/12 $433.87
467734 6/25/12 9/2/12 $663.18
681826 6/13/12 9/4/12 $331.79
531556 6/29/12 9/8/12 $201.65
405671 9/2/12 9/20/12 $83.85
945821 6/30/12 9/23/12 $1,197.00
861568 8/16/12 10/4/12 $193.12
504616 6/20/12 10/8/12 $242.48
174515 9/9/12 10/9/12 $624.76
184664 9/5/12 10/12/12 $198.12
250981 8/22/12 10/24/12 $164.62
290381 8/8/12 10/27/12 $1,190.05
466633 9/5/12 10/28/12 $928.31
867344 8/3/12 10/31/12 $815.44
778652 9/4/12 10/31/12 $770.41
518056 9/5/12 11/8/12 $521.14
188847 10/7/12 11/10/12 $1,116.76
976994 9/13/12 11/11/12 $103.56
473473 8/14/12 11/12/12 $465.20
660565 8/12/12 11/13/12 $366.50
187204 9/14/12 11/15/12 $823.26
373863 8/15/12 11/16/12 $1,022.50
327849 9/6/12 11/21/12 $795.10
962591 8/27/12 11/23/12 $199.42
784380 9/7/12 11/24/12 $120.32
438886 9/26/12 11/27/12 $423.21
997673 10/1/12 12/4/12 $1,089.16
430657 10/17/12 12/5/12 $100.43
923227 9/15/12 12/8/12 $572.08
368662 9/30/12 12/12/12 $464.88
363053 9/24/12 12/24/12 $856.64
393020 10/4/12 12/25/12 $239.93
544996 10/25/12 12/27/12 $696.02
889994 12/6/12 12/29/12 $907.38
623595 11/22/12 12/30/12 $788.29
Bookmarks