function that will set a specified cell to a specified value.

[ryaeger]

Please help me.

I am trying to write a function that will set a specified cell to a specified value. (Code is below).

Let's say my current location is A1. In A1, I enter =SetCellValue(M2, "Bob") expecting M2 to be set to "Bob".
Instead A1 is set to #VALUE!.
Same with =SetCellValue("M2", "Bob"). Debugging shows that the problem occurs at the TargetCell.Value = NewValue
Debugging also shows that TargetCell and NewValue are being passed to my function. Everything is in the same Sheet.
I have made this as simple as I could. but I can't figure out what I am doing wrong.

Function SetCellValue(TargetCell As Range, NewValue As Variant) 
    ' Set the value of the target cell 
    TargetCell.Value = NewValue 
End Function

[jdelano]

This is working as expected given the constraints of a UDF Description of limitations of custom functions in Excel - Microsoft Support

[Paul_Hossler]

User Defined Functions (UDF) cannot change a worksheet, they can only return a value to the cell(s), in this case A1

You would need to use a Sub

The best alternative might be to use a Worksheet_Change event and that's not great

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    Dim r1 As Range    
    If Target.Column > 2 Then Exit Sub    
    Set r1 = Target.Cells(1, 1).CurrentRegion    
    With r1    
        If Target.Row > .Rows.Count Then Exit Sub
        Application.EnableEvents = False
        For iRow = 2 To .Rows.Count
            If Len(.Cells(iRow, 2).Value) = 0 Then
                Worksheets("Sheet2").Range(.Cells(iRow, 1).Value).ClearContents
            Else
                Worksheets("Sheet2").Range(.Cells(iRow, 1).Value).Value = .Cells(iRow, 2).Value
            End If
        Next iRow    
        Application.EnableEvents = True
    End With
End Sub

Sheet1 has addresses and values to go onto Sheet2