how to identify a cell address

I need to find a cell "FIND" then take a number from the 8th column and
allocate this number with some percentage (30%,40%,30%) to the cells above +1
row up, +2 row up, and +3 row up. Using vlookup I'm getting the number bu how
to find the cell's address and the addresses of the cells above to assign
them the %?

Dim rngCell As Range, rngName As Range
Dim num
Set rngName = Worksheets("Legend").Range("C3:J500")
num= Application.VLookup("FIND", rngName, 8, False) ' Ok
Set rngCell = Application.VLookup("FIND", rngName, 8, False) ' the error!!!

Dim rngCell As Range, rngName As Range
Set rngName = Worksheets("Legend").Range("C3:J500")
num= Application.Match("FIND", rngName.Columns(1), 0)
set rngCell = rngName(num,8)
msgbox rngCell.Address

--
Regards,
Tom Ogilvy



"Alex" <Alex@discussions.microsoft.com> wrote in message
news:D957514D-7084-4955-A4DF-B204156CE48C@microsoft.com...
> I need to find a cell "FIND" then take a number from the 8th column and
> allocate this number with some percentage (30%,40%,30%) to the cells above
+1
> row up, +2 row up, and +3 row up. Using vlookup I'm getting the number bu
how
> to find the cell's address and the addresses of the cells above to assign
> them the %?
>
> Dim rngCell As Range, rngName As Range
> Dim num
> Set rngName = Worksheets("Legend").Range("C3:J500")
> num= Application.VLookup("FIND", rngName, 8, False) ' Ok
> Set rngCell = Application.VLookup("FIND", rngName, 8, False) ' the
error!!!