Excel VBA - Referring between ranges

Here's my problem:

I have two ranges, r_products and r_ptypes which are from two different sheets, but of same length i.e.

Set r_products = Worksheets("Products").Range("A2:A999")
Set r_ptypes = Worksheets("SKUs").Range("B2:B999")

I'm searching for something in r_products and I've to select the value at the same position in r_ptypes. The result of Find method is being stored in cellfound. Now, con开发者_开发问答sider the following data:

Sheet: Products
   A          B        C          D 
1  Product
2  S1
3  P1
4  P2
5  S2
6  S3

Sheet: SKUs
   A         B         C          D
1           SKU
2           S1-RP003
3           P1-BQ900
4           P2-HE300
5           S2-NB280
6           S3-JN934

Now, when I search for S1, cellfound.Row gives me value 2, which is, as I understand, 2nd row in the total worksheet, but is actually 1st row in the range(A2:A999).

When I use this cellfound.Row value to refer to r_ptypes.cells(cellfound.Row), It is taking it as an Index value and returns B3 (P1-BQ900) instead of what I want, i.e. B2 (S1-RP003).

My question is how'll I find out the index number in cellfound? If not possible, how can I use Row number to extract data from r_ptypes?

Dante's solution above works fine. Also, I managed to get the index value using built in excel function Match instead of using Find method of a range. Listing it here for reference.

indexval = Application.WorksheetFunction.Match("searchvalue", r_products, 0)

Using the above, I'm now able to refer the rows in r_ptypes

skuvalue = r_ptypes.Rows(indexval).Value

Because .Row always returns the absolute row number of a sheet, not the offset (i.e. index) in the range.

So, just do some minus job to deal with it.

For you example,

r_ptypes.Cells(cellfound.Row - r_ptypes.Cells(1).Row + 1)

or a little bit neat (?)

With r_ptypes
    .Cells(cellfound.Row - .Cells(1).Row + 1)
End With

That is, get the row difference between cellfound and the first cell and + 1 because Excel counts cells from 1.