VBA Code for an accumulator & how to install it correctly.

[peteprp]

Hi,
I have a work sheet in which I want to run VBA code for an accumulator to keep a running total. Unfortunately, VBA code is totally foreign to me.
I have found quite a few sites with VBA code that might work, eg http://www.mcgimpsey.com/excel/accumulator.html
I tried to copy it into the worksheet bit that didn’t work.
So, to the specifics – sheet ‘MSC’!P7:P156 - each cell has a value entered based on a DATEDIF calculation, i.e. it is not user entered. ‘MSC’!Q7:Q156 is where I want to keep the running total for each row.
Is the ‘Two Cell Accumulator’ code in McGimpseys website suitable to use? If so, how do I paste it into ‘MSC’ work sheet?
Any assistance in this would be greatly appreciated.
Thank you.
Pete

 

[StephenCrump]

We'll need a bit more detail about when you want the running totals to recalculate?

One possible scenario might be that each row has a record-specific date, and there is a general calculation date applying to all records? Then perhaps you'd want the totals to update for all records if the user changed the general calculation date, but only for specific record(s) if the user changed record-specific date(s)?

Or perhaps you have something very different in mind?

We'll also need to be clear about what constitutes a change in dates? For example, what if the user starts to edit a date field, but re-enters the same value? Is that a change?

What if the user wants to edit both dates? Do we recalculate after each date change, or wait until both have been changed? Perhaps we ask the user to indicate they are finished making changes, and then recalculate running totals for all records?

 

[peteprp]

Hi Stephen. Thanks so much for responding. Okay, I'll try & explain as briefly as possible. I have a pharmacy stock control program that calculates usage per month, ie total amount dispensed divided by DATEDIF(Start Date, Todays Date, "M"). This works fine and this figure is used to give a reorder point, ie if you use 10/month & you have 10 left, you only have 1 month’s supply on hand and now need to reorder, let’s say 20 for another 2 months stock on hand.
My problem is that this calculation works great if there is always Stock on Hand (SOH) to dispense, but what if an item is out-of-stock (O/S) for 3 months (out of 12 months). Actual usage per month would be reduced by 25% & this will result in an inaccurate reorder point & reorder level.
To try & solve this issue, I have another column with DATEDIF(Start Date, Todays Date, "D"), let’s say it's 365 days. In a column alongside it, I have a formula =IF(SOH<=0, (365-1), 365). So, what I REALLY need is to have a macro to calculate or accumulate (on a daily basis) the total number of days that item is O/S, i.e. 365 total days minus 90 days (O/S) gives 270 days which I would divide by 30.42 to give the actual number of months with SOH greater than zero.
BTW, the Start Date is pre-entered & Todays Date is automatically entered, not user entered.
I hope this all makes sense to you.
Thank you again.
Pete

 

[StephenCrump]

Perhaps something like:

If SOH for any item falls to zero, record the date-out-of-stock (static value)

When SOH reurns to positive - presumably the trigger for this is that a new stock amount is entered into the system
- calculate days OOS as TODAY()- Date-out-of-stock (i.e. if there is a date stored in this field - sometimes new stock will simply top-up a non-zero SOH)
- add this days OOS amount to any pre-existing total days OOS
- clear the date out-of-stock field

Would that work?

I'm not sure how much you'll be able to share your program, but it will help if you describe how the spreadsheet calculates the SOH progression over each day: Start of day SOH - Dispensed + Deliveries = End of Day SOH

It shouldn't take much to outline some VBA to do the job described above. You might end up learning a bit of VBA to adapt it.

 

[peteprp]

I think I understand what you are saying & yes, that should work. My thought was to add 1 to a column for everyday that the SOH was zero & then have a total of all the days that SOH for the item is zero which would then be subtracted from DATEDIF(Sart Date, Todays Date, "D") to give the total of actual days with a SOH>0.
The actual SOH is calculated by SUM(Units Received-Units Dispensed-Units Expired-Units Transferred). The totals for each of these transactions are progressive totals, irrespective of date, & is done using a macro which was very kindly written for me by one of the Mr Excel administrators. I can send you the code if that would help.
Here's what what part of the spreadsheet looks like.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: center; font: 12.0px Calibri}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Calibri}span.s1 {text-decoration: line-through}table.t1 {border-collapse: collapse}td.td1 {background-color: #33cccc ; border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td2 {background-color: #99ccff ; border-style: solid; border-width: 1.0px 0.8px 1.0px 0.8px; border-color: #bfbfbf #000000 #bfbfbf #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td3 {background-color: #33cccc ; border-style: solid; border-width: 1.0px 0.8px 0.8px 0.8px; border-color: #bfbfbf #000000 #000000 #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td4 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td5 {border-style: solid; border-width: 0.8px 1.0px 0.8px 0.8px; border-color: #000000 #bfbfbf #000000 #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td6 {background-color: #ff00ff ; border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000 ; padding: 0.0px 5.0px 0.0px 5.0px}td.td7 {background-color: #00ff00 ; border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #000000 #000000 #000000 #bfbfbf ; padding: 0.0px 5.0px 0.0px 5.0px}td.td8 {background-color: #ffffff ; border-style: solid; border-width: 0.8px 0.8px 0.8px 1.0px; border-color: #000000 #000000 #000000 #bfbfbf ; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]DESCRIPTION - Generic Name of Medication
[/TD]
[TD="class: td1"]UNITS
[/TD]
[TD="class: td2"]Expire date
[/TD]
[TD="class: td3"]TOTAL SOH QTY
[/TD]
[TD="class: td3"]REORDER LEVEL
[/TD]
[TD="class: td3"]RECEIPTS
[/TD]
[TD="class: td3"]ISSUES
[/TD]
[TD="class: td1"]TRANSFERS
[/TD]
[/TR]
[TR]
[TD="class: td4"]Acyclovir Cream 5% 2g
[/TD]
[TD="class: td5"]Per Tube
[/TD]
[TD="class: td6"]Dec-19
[/TD]
[TD="class: td7"]-1
[/TD]
[TD="class: td4"]14
[/TD]
[TD="class: td8"]165
[/TD]
[TD="class: td8"]152
[/TD]
[TD="class: td8"]19
[/TD]
[/TR]
[TR]
[TD="class: td4"]Acyclovir Eye Ointment 3% 4.5g
[/TD]
[TD="class: td5"]Per Tube
[/TD]
[TD="class: td6"]May-20
[/TD]
[TD="class: td7"]0
[/TD]
[TD="class: td4"]16
[/TD]
[TD="class: td8"]200
[/TD]
[TD="class: td8"]197
[/TD]
[TD="class: td8"]5
[/TD]
[/TR]
[TR]
[TD="class: td4"]Adrenaline autoInjector 300mcg
[/TD]
[TD="class: td5"]Per PFS
[/TD]
[TD="class: td6"]Nov-19
[/TD]
[TD="class: td7"]6
[/TD]
[TD="class: td4"]8
[/TD]
[TD="class: td8"]100
[/TD]
[TD="class: td8"]91
[/TD]
[TD="class: td8"]5
[/TD]
[/TR]
[TR]
[TD="class: td4"]Adrenaline Injection 0.1% 1ml (1:1000)
[/TD]
[TD="class: td5"]Per Vial
[/TD]
[TD="class: td6"]Aug-19
[/TD]
[TD="class: td7"]1
[/TD]
[TD="class: td4"]10
[/TD]
[TD="class: td8"]130
[/TD]
[TD="class: td8"]126
[/TD]
[TD="class: td8"]5
[/TD]
[/TR]
</tbody>[/TABLE]
I wish I did know more VBA but its so foreign to me. I'm just a dumbass pharmacist!
I hope this makes sense.
Thank you very much for taking the time to look into this for me.

 

[peteprp]

My apologies - I suddenly realised that I didn't mention there was an additional column in the spreadsheet for Stock Adjustments which can be entered as either a positive or negative amount. Again, this is a progressive total. So, for Acyclovir Eye Oint, there is an amount of +2 that is in the Stock Adjustment column.

 

[peteprp]

My apologies - I suddenly realised that I didn't mention there was an additional column in the spreadsheet for Stock Adjustments which can be entered as either a positive or negative amount. Again, this is a progressive total. So, for Acyclovir Eye Oint, there is an amount of +2 that is in the Stock Adjustment column.