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 (https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1)

[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