Dynamic range

[ProtoVB]

Hi

How can I define a dynamic range that will depend on the cell value of another sheet?

I want VBA to loop into the first sheet and identify the value of the first cell. Then go to the second sheet, identify all the cells that have the same value and define these cells as the range (cells with similar value are adjacent). Thanks

This is the section I need to change:

Please Login or Register  to view this content.

Here is the full script:

Please Login or Register  to view this content.

[tigeravatar]

ProtoVB,

This code will get the ranges defined, but what do you want to do with the ranges once they are defined?

Please Login or Register  to view this content.

[ProtoVB]

Hi tigeravatar and thank you for your post.

Once I have identified the range I want to sum the values in this range and past the value into another sheet.
I will take a close look at your module and say if it does what I want, if I can adapt it or if I am looking for something different

[ProtoVB]

Your script does the job but given my limited understanding of VBA and the complexity of your code I am struggling to adapt the last part, basically calling a function that will sum the value of all the cells in the various offseted ranges and import the data into the Summary sheet.

The problem lies in calling the function (Compile error: ByRef argument type mismatch)

Hi have highlighted the modifications I have done in your script and I have attached the excel file I am working on.

Here is the module with the code

Please Login or Register  to view this content.

Module with the function:

Please Login or Register  to view this content.

[tigeravatar]

ProtoVB,

There's no reason to call a separate function just to sum, this should work for you:

Please Login or Register  to view this content.

[ProtoVB]

Works perfectly, thanks a lot!

[ProtoVB]

Here is a way to run the script by calling functions:

Public variables:

Please Login or Register  to view this content.

The module:

Please Login or Register  to view this content.

The function:

Please Login or Register  to view this content.