Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function
Consider the following example:
1) The whole range below can change, so I need some way of determining the size of the outer range based on the total number rows (I suppose there's a way to do that in VBA w/xldown). 2) Then once I know the outer range, I need to know each one of the inner ranges based on the 'unique' criteria in columnA, and then pass those ranges to a function. So for example, I need to create a function which, whenever the #12 from the 'unique' (off to the left) column exists (I picked up a script that will get me unique values from columnA) and will return to me all the rows that correspond, e.g. if i wanted the range in columnB that corresponds w/all of the rows in columnA that have 12 days, it would be the range B1:B5, and if I wanted all of the rows that corresponded w/25 days it would be the range B6:B11. I tried some things that would concatenate and was able to create the string B1:B5, but the VBA function that I'm passing this range to wouldn't accept a string, and also this didn't seem like the right way to do this. Does anyone have some VBA script that basically does these types of criteria based range creation in the sheet so that you can pass the result to a function? Totally frustrated, any help with this would be massively appreciated. Thanks. column A column B column C days strike volatility unique row 1 12 1360 29.54585 12 row 2 12 1365 29.3065 25 row 3 12 1370 29.25345 56 row 4 12 1375 28.30915 row 5 12 1380 28.10265 row 6 25 1385 27.44215 row 7 25 1390 26.15555 row 8 25 1395 26.32795 row 9 25 1400 26.26365 row 10 25 1405 26.8148 row 11 25 1410 25.50125 row 12 56 1415 24.5996 row 13 56 1420 24.796 row 14 56 1425 24.0346 row 15 56 1430 23.5268 row 16 56 1435 22.87455 row 17 56 1440 22.8891 row 18 56 1445 22.1542 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic range always using the last 10 rows of data | Excel Worksheet Functions | |||
creating a dynamic range name using vba | Excel Programming | |||
Any way to have a dynamic range for ranking, based on criteria? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
copying dynamic range based on cell outside of range | Excel Programming |