Hi Guys,
Last year some time I wrote a macro to sum specific quantities.
Here's how it works:
the data contains song names, composers, prices and quantities sold.
It could have multiples of the same song, the average file is about 50,000 lines.
the macro sorts the file by song name, then composer, then price.
Then it adds up the quantities of all the tracks with the same title, composer and price.
Places the total in the first line.
So for example, if "Yesterday" by the Beatles is in the first 100 lines, it adds them all up, and puts the total quantity in the quantity column of the first line.
It then copies that line to sheet two, and deletes the hundred lines of "Yesterday" from sheet one, so now instead of 100 lines of "yesterday" on sheet one with a quantity of 1 each, I've got one line of "Yesterday" on sheet two with a quantity of 100.
Make sense?
The macro then repeats the procedure for the next song in sheet one.
In the end it usually reduces the file size by about 40,000 lines, making it much easier to work with.
However, sometimes when other people in the office are running the macro it doesn't work.
One person runs it and it sorts the header - it shouldn't
Another runs it and it doesn't sort by the price, only by title and composer - It should do all three.
then sometimes people run it and the quantities don't add up at the end.
But there are two of us in the office, and it works for us ALL the time, 100% correct without fail.
I've tried running the file on the 2 computers myself and the errors keep happening, even though they don't when I run the same file on my machine.
What's causing this?? it's really frustrating.
Any ideas?
thanks,
Paul.
Last year some time I wrote a macro to sum specific quantities.
Here's how it works:
the data contains song names, composers, prices and quantities sold.
It could have multiples of the same song, the average file is about 50,000 lines.
the macro sorts the file by song name, then composer, then price.
Then it adds up the quantities of all the tracks with the same title, composer and price.
Places the total in the first line.
So for example, if "Yesterday" by the Beatles is in the first 100 lines, it adds them all up, and puts the total quantity in the quantity column of the first line.
It then copies that line to sheet two, and deletes the hundred lines of "Yesterday" from sheet one, so now instead of 100 lines of "yesterday" on sheet one with a quantity of 1 each, I've got one line of "Yesterday" on sheet two with a quantity of 100.
Make sense?
The macro then repeats the procedure for the next song in sheet one.
In the end it usually reduces the file size by about 40,000 lines, making it much easier to work with.
However, sometimes when other people in the office are running the macro it doesn't work.
One person runs it and it sorts the header - it shouldn't
Another runs it and it doesn't sort by the price, only by title and composer - It should do all three.
then sometimes people run it and the quantities don't add up at the end.
But there are two of us in the office, and it works for us ALL the time, 100% correct without fail.
I've tried running the file on the 2 computers myself and the errors keep happening, even though they don't when I run the same file on my machine.
What's causing this?? it's really frustrating.
Any ideas?
thanks,
Paul.
