Set a cell value from a function

Solution 1:

Due to Function fundamentals which state that you can not change or set sheet cells. You need to delete the row with Range("A2") = x

EDIT Some additional link (which I believe is always useful to provide for those who want to analyse UDF topic): Creating custom functions by Microsoft

Solution 2:

Yes, you can have your own user defined function which writes values to any cell, not just the one which you type in the formula.

Here is a simple example. The UDF function takes two arguments A and B and returns their product A*B. But what is interesting it returns the result in the adjacent cell just to the right of the cell in which we entered the formula.

Put this code in standard VBA module:

Function UDF_RectangleArea(A As Integer, B As Integer)
    Dim MagicSpell As String
    MagicSpell = "Adjacent(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & A & "," & B & ")"
    Evaluate MagicSpell
    UDF_RectangleArea = "Hello world"
End Function

Private Sub Adjacent(CellToChange As Range, A As Integer, B As Integer)
    CellToChange = A * B
End Sub

Now type in B2 the formula: =UDF_RectangleArea(3,4)

The function returns results in two cells: "Hello world" in B2 (which is not surprising) and the rectangle area in C2 (which is a rabbit out of a hat). Both results as well as the place of "rabbit" appearance can be easily customized. The job is done by the VBA EVALUALTE command. The value of variable MagicSpell becomes in this case Adjacent(C2,3,4) which is fired from within the UDF before the UDF result is returned. Have fun!