0

I wrote 3 functions

CellAbove takes a cell and returns the cell above

CellAboveValueV1 takes a cell and returns the string value of the above cell using .offset(-1,0)

CellAboveValueV2 uses CellAbove to return the string value of the above cell

Here's what my code looks like:

Function CellAbove(r As Range) As Range
    CellAbove = r.Offset(-1, 0)
End Function

Function CellAboveValueV1(r As Range) As String
    CellAboveValueV1 = r.Offset(-1, 0).Value
End Function

Function CellAboveValueV2(r As Range) As String
    CellAboveValueV2 = CellAbove(r).Value
End Function

But when I test this functions with the excel data below, v1 function works, but the v2 function doesn't. What am I missing?

data  | V1(data) | v2(data)
----------------------------
row 1 | data     | #VALUE!
row 2 | row 1    | #VALUE!
row 3 | row 2    | #VALUE!
Daniel Vaca
  • 159
  • 2
  • 3
  • 15
  • 4
    `Set CellAbove = r.Offset(-1, 0)`, missing a `Set`. – BigBen Apr 14 '21 at 18:33
  • That worked. Thanks! – Daniel Vaca Apr 14 '21 at 18:40
  • 1
    One thing to be aware. The functions are not volatile and since they are anchored on the cell below the one it returns, it will not recalc unless the cell to which it refers(the one passed to the function) changes. It will not recalc if the cell above changes as the function is not pointed at it. – Scott Craner Apr 14 '21 at 20:48

0 Answers0