How to select highlighted cells as dynamic range?

[DaedalusT]

Hello, please forgive me if the answer has been posted somewhere else, but I have been looking and I'm not sure how to define my search.

I'm trying to write a function in VBA which allows me to calculate portfolio turnover by looking at changing asset weights. So, I have two questions.

1. Is there a way to write my code to allow for a flexible number of assets? For example, Function Turnover(Asset1, Asset2, ...., Assetn)?

2. Is there a way that allows me to select the range for each asset by highlighting cells, much like the workbook function =sum("":"")? I don't necessarily want to select all the cells in a column.


Thanks for your help
Jason

[Leith Ross]

Hello Jason,

The answer to both questions is yes. To provide you with a solution is going to require more details about the workbook layout.

Since this is your first time here, I am not sure you can post pictures of the worksheets. You may want to upload some sample data and examples of what you want to a public file sharing service. Be sure to post back with the link.

[DaedalusT]

vba1.jpgvba2.jpg
If you look at the first link, in this particular portfolio, I have 4 assets with weights in each column, J:M. However, I want the flexibility to choose any number of assets, even 100 or more.

Also, if you look at the second picture, you'll see the data I selected doesn't go to the bottom of the column, which is why I want to be able to highlight the data manually, flexible for any situation, like I did in the example.

In this example, I would want the result of

(Sum(abs(J5-J4):abs(J34-J33))/30 = SumAsset1 = SA1
then loop Asset 1:Asset 4 and sum the sums, which are columns J:M.

Sorry, I don't have permission to link the addresses.

Thanks