How do I fix a run-time error 13 mismatch on a range value?

Question

The following code creates/updates a date and timestamp for each time I click the command button to save the order/row. "TIMESTAMP" is a named range (Column 106) in the worksheet named: "MASTER". The code is entered within the worksheet object "MASTER". I tried to change Dim r As Range to Dim r As String because Column 106 in the worksheet is formatted as Text, but that didn't help. I also tried to Debug.Print the error line, but I must not be doing that right because I get no results in the Immediate Window.

However, I'm getting a Run-time error '13' - Type mismatch.

1. Open userform and search for an order/row
2. Click save command button
3. The runtime error pops up, so I click 'end', which closes the userform
4. Open the userform again
5. Search for the same order/row
6. Click save command button
7. No error message appears

Thank you in advance for your help.

'***TIMESTAMP***
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim Intersection As Range
    Dim cell As Range
        Set r = Range("TIMESTAMP")
        Set Intersection = Intersect(r, Target)

            If Intersection Is Nothing Then Exit Sub

    Application.EnableEvents = False
        For Each cell In Intersection
            Range(r & cell.Row).Value = Date & " " & Time 'run-time error '13' - Type mismatch
        Next cell
    Application.EnableEvents = True
End Sub

I'm editing this post to show what I've tried with no success.

1. Revision: Dim Intersection As String
2. Compile error: Object required
3. Error line: Set Intersection = Intersect(r, Target)
4. Revision: Removed "Set" in Set Intersection = Intersect(r,Target)
5. Compile error: Type mismatch
6. Error line: If Intersect is Nothing Then Exit Sub
7. Revision: Dim Intersection As Variant
8. Run-time error '91': Object variable or With block variable not set
9. Revision: Set Intersection = Intersect(r, Target)
10. Brings me back to the original problem - Run-time error '13' Type mismatch on line: Range(r & cell.Row).Value = Date & " " & Time

Answer 1

There are probably a handful of ways to go about it, but this seemed to work for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    Dim a As Range
    
    Application.EnableEvents = False
        Sheets("MASTER").Cells(Target.Row, Range("TIMESTAMP").Column).Value = Date & " " & Time
        lr = Sheets("MASTER").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set a = ThisWorkbook.Names("TIMESTAMP").RefersToRange.CurrentRegion
        Set a = Range("TIMESTAMP").Resize(lr)
        a.Name = "TIMESTAMP"
    Application.EnableEvents = True
End Sub

This should update the named range "TIMESTAMP" to include whatever row has been changed.

You should update the sheet references to match your workbook. Let me know if you have any problems or if it's not working as expected.

Good luck!