In Excel user-defined function, how to edit value of the next cell

I want to write a user-defined function (defined in a workbook's module) which takes a range of cells and generate value for the next cell. It looks like this:

Function doStuff(ByRef input As Range) As Integer
   Dim columnCount As Integer
   Dim nextCell As Range 

   columnCount = input.Columns.Count

   ' nextCell is 2 cells away on the right of the last cell on the first row of input
   Set nextCell = input.Cells(1, columnCount).Offset(0, 2)
开发者_如何学编程   nextCell.Value = "doStuff"

   doStuff = columnCount
End Function

In cell A2, if I use the formula =doStuff(A1), I expect A2=1 and A3="doStuff". Somehow I always got the error "Application-defined or object-defined error" when it got to the nextCell.Value = "doStuff" line. I'm I doing it correctly or is there a workaround this? Thank you.

From the worksheet, functions can only return values (and a few other things). That is, they can only change the Value property of the Range from which they're called. They cannot change any other property of that Range, any property of any other Range, nor any property of Worksheet, Workbook, or Application objects.

You can use the change event to detect when that formula is entered, like so

Function doStuff(ByRef rInput As Range) As Long

   doStuff = rInput.Columns.Count

End Function

And in the worksheet's class module

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.HasFormula Then
        If LCase(Target.Formula) Like "=dostuff(*" Then
            Target.Cells(1, Target.DirectPrecedents(1).Value).Offset(0, 2).Value = "do stuff"
        End If
    End If

End Sub

I'm not sure if that logic is right because I couldn't quite understand where you wanted to write the text, but I'm sure you can figure that part out.