referencing active cell works in a sub but not in a custom function

[RITCHI]

Hi
Apologies if this has appeared twice.

I want to turn the following sub into a custom function but
substituting Sub Choice() with Function Choice() and End Sub with End
Function didn't work.

Any help would be gratefully received

Sub Choice()
Dim RowRand As Range
Set RowRand = ActiveCell.Offset(0, -1)
Select Case RowRand
Case 45 To 50
ActiveCell = ActiveCell.Offset(0, -10)
Case Is > 50
ActiveCell = ActiveCell.Offset(0, -9)
Case Else
ActiveCell = 27.89
End Select
End Sub


Ritchi

 

[Guest]

Try this instead:
Function Choice(anyCell As String) As Single
Application.Volatile
Dim RowRand As Single
RowRand = Range(anyCell).Offset(0, -1)
Select Case RowRand
Case 45 To 50
Choice = Range(anyCell).Offset(0, -10)
Case Is > 50
Choice = Range(anyCell).Offset(0, -9)
Case Else
Choice = 27.89
End Select
End Function

In the cells on the worksheet, call it this way (this is one way)
=choice(ADDRESS(ROW(),COLUMN()))

ROW() and COLUMN() return the row # and column # of the cell they are used
in, so the formula works anywhere you put it. ADDRESS returns a string
representation of the address as $F$11, $A$1 etc.

I presume you're aware that an error will occur if the column offsets of -1,
-9 or -10 result in pointing to a column to the left of column A since they
don't exist. Means the formula cannot cannot be used in columns A through J
without failing.