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" wrote in message
...
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!!!