Extract subrange from a range

[azer]

Hello and thank you for your time and help,

I am trying to create a function which would extract a subset from a given range depending on the values contained in another range. The final aim is to be able to use the Percentrank function on dynamically changeable groups

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]GPB[/TD]
[TD]Group[/TD]
[TD]Percentrank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Albania[/TD]
[TD]5000[/TD]
[TD]1[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]France[/TD]
[TD]28000[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Romania[/TD]
[TD]6000[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]US[/TD]
[TD]34000[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The idea would to have something like Percentrank(subset(B2:B4, C2:C4, 1), B2) with the subset function returning the subset as a range.

I have started with something like this but am stuck on a few issues:

Function Subset(x As Range, y As Range, V As String) As Range


Dim z As Range
Dim c As Range


For Each c In x

' I don't see how to refer to the equivalent cell in y:
If ??.Value = Valeur Then


If Not z Is Nothing Then
Set z = Union(z, c)
Else
Set z = c
End If
End If
Next

'Even with a simple example I am unable to return a range:

Subset = z


End Function


Thank you in advance for your help

 

[azer]

Here's my latest try which doesn't work:

Function Subs(x As Range, y As Range, V As Integer) As Range

Dim z As Range
Dim c As Cell
Dim n As Integer

    For Each c In x.Cells[INDENT]Set n = c.Row[/INDENT]
[INDENT]If y(1, n).Value = Valeur Then[/INDENT]
[INDENT=2]If Not z Is Nothing Then[/INDENT]
[INDENT=3]Set z = Union(z, c)[/INDENT]
[INDENT=2]Else[/INDENT]
[INDENT=3]Set z = c[/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT]End If[/INDENT]
    Next

Subs = z

End Function

Any thoughts?