Posts by Peter B

Re: Macro to open Word and Start Mail Merge

Well yes that is the option.

I justed wanted to simplify it so it would work at the click of ONE button, rather than have to search for a word sheet, click the merge, click the print icon.

Hi Guys

I currently have an excel spreadsheet which populates some data into a Word Mail Merge Document.

Does anybody know of a macro in which I could

a) Open the specific Word document directly from excel
and then
b) Run the Mail Merge
and then
c) Print the results of the document

I would like this all to happen with the running of the macro. It would save the user from looking for the word doc etc

If anybody could help this would be great

Re: MS Query refresh with Macro

Thanks.

It works !

You wouldn't happen to know how to autofill a formula in a particular column based on the amount of rows of data returned from the query ?

For example, if the query returns 14 rows of data what code do I need to write so that it automatically carries down a formula to the last line of data ?

Sorry this may be for a new thread

Peter

Hi all

I use MS query extensively. Does anybody know if it possible to create a MACRO which would run the query refresh upon the click of a button (assigned to a macro). I have several query's in a spreadsheet and each needs to be manually updated (by right clicking in the query and hitting refresh now)

I have tried to record a macro to do this but I always get the error 'Application defined or object defined error'.

Any help on this would be appreciated.

Re: FIFO calculation

Hi Alan

That has worked a treat !

Happy Days.

BTW I am not working back here late at night. I am actually located in Sydney and we are averaging 30 degree days at the moment. Now that I have this sorted I can head off to the beach after work !

Thanks again for all your effort on this one.

Peter

Re: FIFO calculation

Hi Alan

I have the macro working now. It still doesnt seem to return the exact data that I would like. I have run the data for a different asset group just as a test.

I have attached the file with another tab called "Results that should be shown". The macro as it stands is not calculating the items in red being the residual amount of units from that batch that are redeemed.

If you note, column N now adds to column I.

It could be asking a lot and I know I have bombarded you with e-mails but is it possible for the macro to be amended to do this ?

Regards
Peter

Re: FIFO calculation

The issue it seems is to do with the xlsortnormal in the code. I am running excel 2000 and it doesn't know how to read this.

I am assuming you are running 2002.

Umm (head scratch) not sure what to do now.....

Peter

Re: FIFO calculation

Hi Alan

Thanks for your hard work on this one.

I just cant seem to get the macro to run. I have read the VB behind it and can't debug it.

I assume it was running perfectly well when you sent it so I am struggling to work out the issue.

If you have the time perhaps you could look over it again.

That would be much appreciated. Oh and thanks again for working on this one

Peter

Re: FIFO calculation

Hi Alan

I have attached an example sheet of the data that I need a FIFO calc for.

All the redemptions are highlighted in red. If you look at the first redemption (i46) of 37352.22 units it will be coming out of the units purchased in cells i51 and then i50. I need the calc to show how many of the units in i50 remain from that purchase.

Then the spreadsheet needs to look at the next redemption (cell i38) and work out how much of i50 or the next purchase lot is consumed.

I really hope this is making sense and I hope I am not confusing you more.

Re: FIFO calculation

Hi Alan

Each transaction has a unique transaction id. To keep it simple we log the transaction on the 15/12/04 as transaction id 1 and so on.

If possible I need the report to identify each original purchase that has been completely redeemed and the amount of a residual. For example if we had a purchase of 5000, 6000 and 3000 and then we redeemed 12759 units I would like it to show.

5000 (Units redeemed)
6000 (Units redeemed)
1759 (Units redeemed)
1241 (Units Remaining)

This would help greatly in determining original unit cost.

Then for the next redemption I would like it to consider that there is 1241 Units remaining and move on and do the next calc.

If this is not clear I can provide a spreadsheet which has my core data.

Cheers
Peter

Hi all

I am a newbie here so please bear with me.

I have a simple calc that I would like to do.

I have a purchase history of various stock. It is simply listed as a purchase date and amount and shown as positive. The next cell down is the same concept. This continues until there is a sale which shows as a negative.

Does anybody know of a way that excel can scroll up and down the spreadsheet and work out which original batch the purchase inventory was from ? This is an example of my list.

transaction_datetime effective_datetime amount units
16/02/05 12:22 11/02/05 0:00 48106.41 45452.41
09/02/05 15:10 04/02/05 0:00 7176.44 6774.70
02/02/05 8:10 28/01/05 0:00 9123.26 8670.65
25/01/05 17:13 21/01/05 0:00 9627.53 9164.71
24/01/05 16:17 24/01/05 0:00 124.16 118.09
24/01/05 16:10 21/01/05 0:00 8857.29 8409.09
21/01/05 15:31 17/01/05 0:00 2604.13 2466.03
12/01/05 16:08 07/01/05 0:00 28295.23 26696.13
05/01/05 16:48 31/12/04 0:00 54609.33 51605.87
30/12/04 14:52 24/12/04 0:00 -2143.32 -2026.01
20/12/04 14:28 16/12/04 0:00 110.21 104.57
15/12/04 13:10 10/12/04 0:00 -8803.81 -8390.97

For example if we bought 2000 units, then 4000 the next day and sold 3000 on the third day.

How could excel work out that the sold units consisted of 2000 from the original batch and then another 1000 from the second batch. It also means that there is 3000 units from the second batch left on hand.

Sorry it is long winded

Yes, each attached worksheet carries several formulas which will need to update each and every time a purchase is made.

For example if an apple is sold it needs to reduce the inventory by one apple. Also it adds 1x the profit for one apple etc Records the date of the purchase amongst other things

Ermm I am not sure how to be more specific. If you picture the input cells in one sheet and the formulas in another it is best. Input 1 next to apple, 2 next to oranges etc. Then run the macro and it fills formulas (in each worksheet according to the input data). For example if the input spredsheet had one apple and then the macro was run it would fill a formula for one row only. If it was 2 apples then fill formulas for two rows. 50 apples then fifty rows.

Sorry I cant attach an example as it is only a prob I have been going with through my mind

I have found something similar at http://www.mvps.org/dmcritchie/excel/insrtrow.htm.

Sorry I cant be clearer. If you need anything further, let me know

Hi guys

I think this one should be quite easy for those in the know.

What I am looking to do is have a macro that inserts rows and fills formulas. I have a mastersheet where all the data is input. For this example it is a fruit worksheet. It will contain a list of all fruits sold daily.

For example one apple is purchased, two oranges and three pears.

I would like the macro to recognise that 1 apple has been purchased and go to the apple worksheet, insert 1 row (under the current rows) and fill the formula for one row only. Likewise for oranges, insert two rows (in the oranges worksheet) and copy down two formulas. Pears three rows and fill formulas. And so on...

I need it to recognise that each fruit purchase may change daily and only to insert enough rows for that days purchase. It needs to append each worksheet (as I need to keep a masterlist of all purchases). Also it needs to be able to recognise where the last formula in a worksheet is and to be able to copy it down.

Any help on this one would be greatly appreciated.