Macro to Total Volumes

**aftabn10** · 12-23-2010, 06:08 AM

Hi, i am looking to write a macro that will loop through a list of queues and then sum up the values for each queue from column S and AA from a different.

I have attached a sample workbook that has 2 sheets. What i would like is for the macro to go through the list of queues on Column A in Volumes Sheet and then find these queues within the Source Sheet and if found sum up the values in Column S and AA and then put the value in Col D on Volumes Sheet, otherwise if the queue is not there then move onto the next cell.

I am guessing I will have to write a for each loop but not sure where to start.

Thanks in advance. Hope somebody can help.

**watersev** · 12-23-2010, 06:24 AM

hi, it can be easily done by formula. Is macro obligatory?

**Marcol** · 12-23-2010, 06:27 AM

I can't work out what your aim is here.

Does this workbook help?

**aftabn10** · 12-23-2010, 06:47 AM

watersev and marcol, thanks for your help, the reason for the macro is that this needs to be ran every 2 hours and the source file gets changed so would need the formulas to be converted to values. I thought that i would be able to use a for each loop (i.e each queue that appears search for values in column s and aa and sum these up).

thanks for your help guys.

**watersev** · 12-23-2010, 06:54 AM

it may be loop code but why if you can use SUMIF function...

**Marcol** · 12-23-2010, 08:39 AM

Try this in a standard module

Please Login or Register  to view this content.

And in the sheet module Sheets("Volumes")

Please Login or Register  to view this content.

Click on "Update Sheet" in Sheets("Volumes") B2 to run the macro

I have not put in any code to avoid over writing data entries already made as yet.

Hope this helps

**aftabn10** · 12-31-2010, 05:48 AM

Marcol, apologies for the delay in responding. I have tried running the sample file you have attached and i get a debug error at the following point:

.Cells(4, ColNo).Select

but when adding the code to my file this works fine, just wondering what the error is within the file you have attached. Also within your file you have attached a timestamp, would this mean for example if the time is over 10:00 then the data would be pasted within the correct column i.e. 10:00 is in Column E.

Thanks for your help, really appreciated.

**Marcol** · 12-31-2010, 07:37 AM

......and i get a debug error at the following point:

.Cells(4, ColNo).Select

My oversight, the code was designed to allow updating up to 1 hour after the required time, if say you need the values for 10:00, then you can get these figures any time between 10:00 and 10:59:59. when the time goes to 11:00 or over, you can't get another update until 12:00. This is where the error lies.

Change the code to this

Please Login or Register  to view this content.

Please Login or Register  to view this content.

is now inside the if statement.

The line is probably redundant as it is followed by another selected cell in the sheet change event.

You can add to the sampling times if you need to but the code is only valid for whole hours e.g. 09:00, 10:00, etc.

The "Time Stamp" is not automatic, it is updated by any change on the sheet, or by Formulas > Calculate Now. Do you need it to be automatic while the sheet is active?

Does this clarify things for you?