R
rajansood
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
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