how to identify a cell address

[Guest]

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!!!

 

[Tom Ogilvy]

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

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!!!