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