Range within another Range

[Guest]

Howdy,

I have two ranges (smallRange and bigRange) that I am working with, both are
1 column wide and variable (dynamic) in length but bigRange is always longer
than smallRange.

Now for the twist, the values in the smallRange refer to the row in the
bigRange, and I want the value from the bigRange. Any thoughts/suggestions

Regards,
Tim

 

[Tim Williams]

Not sure what you mean by "refer to". You can refer to individual
cells in a range using
bigRange.cells(x)

if that's what you're after. Since they are single-column ranges
cells(5) will be the fifth row. If this doesn't help maybe you can
provide more details.

Tim.

 

[Guest]

Tim

Thanks for the feedback. What I mean is best illustrated by an example.
smallRange
1
5
10

bigRange
84
63
215
500
1
12
78
105
53
6

For the three smallRange values, I want the value from the bigRange
(84,1,6). The smallRange value is the row in the bigRange where the value
that I want resides.

HTH

regards
Tim

 

[john.topley]

As per TW reply:

Sub RangeWithinRange()

Set smallRange = Range("smallrange")
Set bigRange = Range("bigrange")

For Each cell In smallRange
MsgBox bigRange.Cells(cell.Value)
Next

End Sub

HTH.