Vba To create End Of Year Write downs on workssheets in a range

[coxycoxycoxy]

Hi All

I have been asked at work to create a write down function on a excel workbook that we currently use for a Stock System,
the work book itself has become very bulky and complicated and I Know that Excel is not the best option as it is not a Relational Database.
however for the time being I have to find a way to solve the immediate problem. Below is two Worksheets fig1 is a example of a stock card as they are at the moment & fig2 is where I need to get to. or produce a function that would adchive the overall result.


Overall Result

Produce an automated procedure that can identify the last entries for previous years, then subtract a percenatge from the closing value. the "write Down" must be shown
on the stock card/worksheet

Description of VBA Function

The Vba will need to be able to identify the last entry for any year other than the current year & insert a row "end of year in Fig2" and the last value in
column "M" before the inserted line and deduct 10% for each end of year value.


One Problem that i can see is at the moment the running total value in column "M" is summed as follows "=SUM($L$10:L11)" through to "SUM($L$10:L40)"
once the end of year line has been inserted it would need to sum the end of year values and then the running totals for the current year. and not all the value in cloumn "M" as it currently Does.


If anyone can help i can forward a copy of the two works sheets for you to look at properly, and any help if only advice or partial would be Greatly appricahated as I am 1. Really out of my depth now and 2. against time.




Thanks in advance

Mark

FIG1

                          B  C  D/E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T    2                                                                                                                                 Bitmap                  HISTORY              3  PRODUCT CODE:  SH 0.254X457X305MM ALLOY 42            SPEC:  ALLOY 42 HALF HARD               #                4                                                  UOM       5  SIZE:  457MM X 305MM X 0.254MM                           8609  TOTAL STOCK  8609.000  EACH        6                                ########  TOTAL VALUE  £40,290.12          7                                                    8  LOCATION  GRN  IN / OUT DATE  CAST / LOT NO.  CUSTOMER /    SUPPLIER  PO / SA NO.  COST PRICE   STOCK IN / OUT  ACTUAL STOCK  TOTAL COST   RUNNING TOTAL  SALE PRICE  COMMENTS    9  JADE    10     6671  24/11/2008  w734L6     9668  £4.45  3030  3030  £13,483.50  £13,483.50          11     6671  13/07/2008  W734L6     10675  £4.45  -100  2930  -£445.00  £13,038.50          12     6671  08/08/2009  w734L6     12928  £4.45  -2930  0  -£13,038.50  £0.00          13     7684  18/10/2009  563962     10464  £4.68  5079  5079  £23,769.72  £23,769.72          14     6671  24/11/2010  w734L6     9668  £4.45  3030  8109  £13,483.50  £37,253.22     14  X 81.647 KGS, 1 X 39.917    15     6671  13/07/2011  W734L6     10675  £4.45  -100  8009  -£445.00  £36,808.22          16     6671  08/08/2011  w734L6     12928  £4.45  -2930  5079  -£13,038.50  £23,769.72          17     7684  18/10/2011  563962     10464  £4.68  5079  10158  £23,769.72  £47,539.44          18     7684  11/11/2011  563962     13276  £4.68  -3978  6180  -£18,617.04  £28,922.40          19     7694  11/11/2011  563962     10465  £4.68  2593  8773  £12,135.24  £41,057.64          20     7694  21/11/2011  563962     11008  £4.68  -164  8609  -£767.52  £40,290.12          21                          8609  £0.00  £40,290.12          22                          8609  £0.00  £40,290.12          23                          8609  £0.00  £40,290.12          24                          8609  £0.00  £40,290.12          25                          8609  £0.00  £40,290.12          26                          8609  £0.00  £40,290.12          27                          8609  £0.00  £40,290.12          28                          8609  £0.00  £40,290.12          29                          8609  £0.00  £40,290.12          30                          8609  £0.00  £40,290.12          31                          8609  £0.00  £40,290.12          32                          8609  £0.00  £40,290.12          33                          8609  £0.00  £40,290.12          34                          8609  £0.00  £40,290.12          35                          8609  £0.00  £40,290.12          36                          8609  £0.00  £40,290.12          37                          8609  £0.00  £40,290.12          38                          8609  £0.00  £40,290.12          39                          8609  £0.00  £40,290.12          40                          8609  £0.00  £40,290.12          41  DO NOT  INSERT BELOW THIS LINE

FIG 2

<colgroup><col style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;" width="24"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" width="74"> <col style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;" width="45"> <col style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" width="37"> <col style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;" width="38"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" width="71"> <col style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;" width="112"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" width="54"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;" width="83"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;" width="25"> <col style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" width="57"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <col style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" width="37"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" width="58"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" width="26"> <tbody><tr style="height: 21.75pt; mso-height-source: userset;" height="29"> <td style="border: 0px black; width: 18pt; height: 21.75pt; background-color: rgb(219, 229, 241);" class="xl453" height="29" width="24"><strong><font face="Calibri">

  B  C  D/E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T    2                                                                                                                                 Bitmap                  HISTORY              3  PRODUCT CODE:  SH 0.254X457X305MM ALLOY 42            SPEC:  ALLOY 42 HALF HARD               #                4                                                  UOM       5  SIZE:  457MM X 305MM X 0.254MM                           8609  TOTAL STOCK  8609.000  EACH        6                                £40,290.12  TOTAL VALUE  £40,290.12          7                                                    8  LOCATION  GRN  IN / OUT DATE  CAST / LOT NO.  CUSTOMER /    SUPPLIER  PO / SA NO.  COST PRICE   STOCK IN / OUT  ACTUAL STOCK  TOTAL COST   RUNNING TOTAL  SALE PRICE  COMMENTS    9  JADE    10     6671  24/11/2008  w734L6     9668  £4.45  3030  3030  £13,483.50  £13,483.50          11     6671  13/07/2008  W734L6     10675  £4.45  -100  2930  -£445.00  £13,038.50          12     END  OF YEAR 2008                          £0.00  £13,038.50                         13     6671  08/08/2009  w734L6     12928  £4.45  -2930  0  -£13,038.50  £0.00          14     7684  18/10/2009  563962     10464  £4.68  5079  5079  £23,769.72  £23,769.72          15     END  OF YEAR 2009                          £0.00  £23,769.72                         16     6671  24/11/2010  w734L6     9668  £4.45  3030  8109  £13,483.50  £37,253.22     14  X 81.647 KGS, 1 X 39.917    17     END  OF YEAR 2010                          £0.00  £37,253.22                         18     6671  13/07/2011  W734L6     10675  £4.45  -100  8009  -£445.00  £36,808.22          19     6671  08/08/2011  w734L6     12928  £4.45  -2930  5079  -£13,038.50  £23,769.72          20     7684  18/10/2011  563962     10464  £4.68  5079  10158  £23,769.72  £47,539.44          21     7684  11/11/2011  563962     13276  £4.68  -3978  6180  -£18,617.04  £28,922.40          22     7694  11/11/2011  563962     10465  £4.68  2593  8773  £12,135.24  £41,057.64          23     7694  21/11/2011  563962     11008  £4.68  -164  8609  -£767.52  £40,290.12          24     END  OF YEAR 2011                       8609  £0.00  £40,290.12                         25                          8609  £0.00  £40,290.12          26                          8609  £0.00  £40,290.12          27                          8609  £0.00  £40,290.12          28                          8609  £0.00  £40,290.12          29                          8609  £0.00  £40,290.12          30                          8609  £0.00  £40,290.12          31                          8609  £0.00  £40,290.12          32                          8609  £0.00  £40,290.12          33                          8609  £0.00  £40,290.12          34                          8609  £0.00  £40,290.12          35                          8609  £0.00  £40,290.12          36                          8609  £0.00  £40,290.12          37                          8609  £0.00  £40,290.12          38                          8609  £0.00  £40,290.12          39                          8609  £0.00  £40,290.12          40                          8609  £0.00  £40,290.12          41                          8609  £0.00  £40,290.12          42                          8609  £0.00  £40,290.12          43                          8609  £0.00  £40,290.12          44  DO NOT  INSERT BELOW THIS LINE

</tbody>

 

[venkat1926]

depends upon the configuration of data

if you want the last day of the year
it is one day previous to the first day of next year
you have consider the last day of this year is a working day for stocks.

once you get the data of last day then other manipulation can be done

on this basis write your macro

if problem post a SMALL EXTract of the data