Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing active cell works in a sub but not in a custom function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
referencing active cell works in a sub but not in a custom functi
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. "RITCHI" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
HYPERLINK IF referencing the cell of the function.. | Excel Worksheet Functions |