dynamic range based on criteria, within a dynamic range, passed to a function

[rajansood]

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

 

[dan dungan]

Why not use the autofilter?

 

[rajansood]

Why not use the autofilter?





- 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?

 

[rajansood]

Why not use the autofilter?





- 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?

 

[Alan Beban]

I'm not sure I understand exactly what you are trying to do, but if the
functions in the freely downloadable file at
http://home.pacbell.net/beban are 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

 

[rajansood]

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







- Show quoted text -

alan, i think your functions could probably do it, can u PLEASE email
me at (e-mail address removed), i can send you an example spreadsheet... thanks
a lot.