greglittle
09-27-2012, 11:41 AM
Can someone please check this formula to see why I keep getting a #VALUE error when I use it in the sheet? Thanks!
Function CAGR(rng As Range) As Double
Dim firstCell As Range
Dim lastCell As Range
Dim firstVal As Double
Dim lastVal As Double
firstCell = rng.Cells(1, 1)
lastCell = rng.Cells(1, rng.Count)
firstVal = Range(firstCell).Value
lastVal = Range(lastCell).Value
Debug.Print firstCell, lastCell, firstVal, lastVal
CAGR = (lastVal / firstVal) ^ (1 / rng.Count) - 1
End Function
Also, does anyone know how to use Debug.Print to see what the values of the variables are for a function (like this) that is used as a formula in the sheet? I've never had the need to see variable values in this case (and it didn't work!).
Function CAGR(rng As Range) As Double
Dim firstCell As Range
Dim lastCell As Range
Dim firstVal As Double
Dim lastVal As Double
firstCell = rng.Cells(1, 1)
lastCell = rng.Cells(1, rng.Count)
firstVal = Range(firstCell).Value
lastVal = Range(lastCell).Value
Debug.Print firstCell, lastCell, firstVal, lastVal
CAGR = (lastVal / firstVal) ^ (1 / rng.Count) - 1
End Function
Also, does anyone know how to use Debug.Print to see what the values of the variables are for a function (like this) that is used as a formula in the sheet? I've never had the need to see variable values in this case (and it didn't work!).