Getting Totals from multiple sheets VBA

Hi,

I am looking for the correct way to find cell value in multiple sheets and return total to offset cell.

I am only get 1st occurrence of value, but need to get all occurrences of value

What I am using is not obviously correct but a start.

Can someone show me the correct way to do this.

Please Login or Register  to view this content.

Thank you for your time

Have you tried stepping through the code?

You can use the Sumif Function in VBA,
Since you know what ranges you want in each sheet you can set the ranges in the code.
This example code will Sum the number 3 in your specified ranges.
You can edit the code to use your offset designations.

Please Login or Register  to view this content.

Try it like so.... I've created a couple of arrays to hold your sheetnames and your address strings to shorten up the code a bit.

Please Login or Register  to view this content.

I made a correction and highlighted it in red in post #4.

I like to think of arrays as just a "list" and each item in the list can be grabbed by "index number". So, the first time through the array loop, we take the first indexed item in BOTH arrays and use them as sheet name and range references. Next time through we take the next pair, etc.


The correction I made above is because I forgot that arrays are usually 0-based, the first index number is 0, not 1. By using LBound(), I don't have to remember which it is, the macro simply uses the lowest index number for the array whether it is 0 or 1.

Yes, the product is being searched in each sheet listed in array 1, but only in the range listed in array2 in the same position within the arrays. But it duplicates your original long macro in that way.

Please Login or Register  to view this content.