| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range based on criteria, within a dynamic range, passed to a function
consider the following example:
i need to get excel to create a dynamic range based that can determine the number of consecutive rows in the below array (e.g. 18) , the number of rows may change (increase/decrease). once that range is determined i need a function that can create a dyamic range based on some criteria (in this example, 'days'). in colume E i'm able to find the unique days from column A. so, if there were some way to find all the ranges associated w/each 'day' that would be ideal. for example, if i were to chose the# 12 from column E, some function would return back the array, B1:B5, or if i were to chose# 25 in column E, it would return B6:B11. I tried to do this with COUNT and create a string B6:B11, but was unable to pass it to the function I was using. if there is any way to do this in VBA, any tips would be greatly appreciated. column A column B column C column E 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dynamic range based on criteria, within a dynamic range, passed to a function
Why not use the autofilter?
On Oct 9, 9:50 am, wrote: consider the following example: i need to get excel to create a dynamic range based that can determine the number of consecutive rows in the below array (e.g. 18) , the number of rows may change (increase/decrease). once that range is determined i need a function that can create a dyamic range based on some criteria (in this example, 'days'). in colume E i'm able to find the unique days from column A. so, if there were some way to find all the ranges associated w/each 'day' that would be ideal. for example, if i were to chose the# 12 from column E, some function would return back the array, B1:B5, or if i were to chose# 25 in column E, it would return B6:B11. I tried to do this with COUNT and create a string B6:B11, but was unable to pass it to the function I was using. if there is any way to do this in VBA, any tips would be greatly appreciated. column A column B column C column E 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dynamic range based on criteria, within a dynamic range, passed to a function
On Oct 9, 1:13 pm, dan dungan wrote:
Why not use the autofilter? On Oct 9, 9:50 am, wrote: consider the following example: i need to get excel to create a dynamic range based that can determine the number of consecutive rows in the below array (e.g. 18) , the number of rows may change (increase/decrease). once that range is determined i need a function that can create a dyamic range based on some criteria (in this example, 'days'). in colume E i'm able to find the unique days from column A. so, if there were some way to find all the ranges associated w/each 'day' that would be ideal. for example, if i were to chose the# 12 from column E, some function would return back the array, B1:B5, or if i were to chose# 25 in column E, it would return B6:B11. I tried to do this with COUNT and create a string B6:B11, but was unable to pass it to the function I was using. if there is any way to do this in VBA, any tips would be greatly appreciated. column A column B column C column E 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- Hide quoted text - - Show quoted text - i need to pass the array i get back to an interpolation function... i didn't think i could do that via autofilter, can i? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dynamic range based on criteria, within a dynamic range, passed to a function
On Oct 9, 1:13 pm, dan dungan wrote:
Why not use the autofilter? On Oct 9, 9:50 am, wrote: consider the following example: i need to get excel to create a dynamic range based that can determine the number of consecutive rows in the below array (e.g. 18) , the number of rows may change (increase/decrease). once that range is determined i need a function that can create a dyamic range based on some criteria (in this example, 'days'). in colume E i'm able to find the unique days from column A. so, if there were some way to find all the ranges associated w/each 'day' that would be ideal. for example, if i were to chose the# 12 from column E, some function would return back the array, B1:B5, or if i were to chose# 25 in column E, it would return B6:B11. I tried to do this with COUNT and create a string B6:B11, but was unable to pass it to the function I was using. if there is any way to do this in VBA, any tips would be greatly appreciated. column A column B column C column E 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- Hide quoted text - - Show quoted text - i need to pass the array i get back to an interpolation function... i didn't think i could do that via autofilter, can i? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
dynamic range based on criteria, within a dynamic range, passed to a function
On Oct 9, 2:02 pm, Alan Beban wrote:
I'm not sure I understand exactly what you are trying to do, but if the functions in the freely downloadable file athttp://home.pacbell.net/bebanare available to your workbook, the following, array entered into a column of cells long enough to accommodate the output will return 1360 1365 1370 1375 1380 =VLookups(E1,INDIRECT("A1:C" & COUNTA(A:A)),2) and the following will return the same array to arr, which could then be passed to a function: Sub abtest5() Dim arr() 'This will be the array to be passed to a function Dim arrTotal() 'This will be an array of the total data range Dim arrU() 'This will be the vertical array of unique 'values from column A of the data range Dim x As Long x = Application.CountA(Range("A:A")) arrU = ArrayUniques(Range("A1:A" & x)) arrTotal = Range("A1:C" & x) arr = VLookups(arrU(1, 1), arrTotal, 2) End Sub To return the array corresponding to 25 in Column A you would change arrU(1,1) to arrU(2,1); i.e., the second element in the array of unique values. Post back if i've misunderstood. Alan Beban wrote: consider the following example: i need to get excel to create a dynamic range based that can determine the number of consecutive rows in the below array (e.g. 18) , the number of rows may change (increase/decrease). once that range is determined i need a function that can create a dyamic range based on some criteria (in this example, 'days'). in colume E i'm able to find the unique days from column A. so, if there were some way to find all the ranges associated w/each 'day' that would be ideal. for example, if i were to chose the# 12 from column E, some function would return back the array, B1:B5, or if i were to chose# 25 in column E, it would return B6:B11. I tried to do this with COUNT and create a string B6:B11, but was unable to pass it to the function I was using. if there is any way to do this in VBA, any tips would be greatly appreciated. column A column B column C column E 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- Hide quoted text - - Show quoted text - alan, i think your functions could probably do it, can u PLEASE email me at , i can send you an example spreadsheet... thanks a lot. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic range based on another column | Setting up and Configuration of Excel | |||
| creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | 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 | |||
Linear Mode
