excelvbaruntime-errornamed-ranges# How do I fix a run-time error 13 mismatch on a range value?

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.

- Open userform and search for an order/row
- Click save command button
- The runtime error pops up, so I click 'end', which closes the userform
- Open the userform again
- Search for the same order/row
- Click save command button
- 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.

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

Solution

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!

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel