excelvbaruntime-errorworksheet# Type 13 mismatch error when erasing more than one target cell

I am fairly new to VBA, please forgive me. This code is probably bloated and can be cleaned up (not sure how), but when I have multiple cells in the target filled with a value that fires the worksheet change event, I can delete them one at with no issue. If I delete more than one cell at a time, I end up with the type 13 mismatch error. The code is supposed to check for a value change in column c, if it is one of two values it is supposed to insert "N/A" into several other cells on that same row (some non-contiguous). If it is not that value, then it is supposed to remain blank. It is paired with a code to check that same column for a third value, if it is the third value, it is supposed to return "N/A" to a single cell in that row. The error is highlighting "If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then" as the issue here.

I am fairly new to VBA, please forgive me. This code is probably bloated and can be cleaned up (not sure how), but when I have multiple cells in the target filled with a value that fires the worksheet change event, I can delete them one at with no issue. If I delete more than one cell at a time, I end up with the type 13 mismatch error. The code is supposed to check for a value change in column c, if it is one of two values it is supposed to insert "N/A" into several other cells on that same row (some non-contiguous). If it is not that value, then it is supposed to remain blank. It is paired with a code to check that same column for a third value, if it is the third value, it is supposed to return "N/A" to a single cell in that row. The error is highlighting "If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then" as the issue here.

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Sheet4
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Wind Sites" Or Target.Value = "Springbok 3" Then
Target.Offset(0, 2) = "N/A"
Target.Offset(0, 3) = "N/A"
Target.Offset(0, 4) = "N/A"
Target.Offset(0, 5) = "N/A"
Target.Offset(0, 7) = "N/A"
Target.Offset(0, 9) = "N/A"
Target.Offset(0, 10) = "N/A"
Else
Target.Offset(0, 2).ClearContents
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
Target.Offset(0, 5).ClearContents
Target.Offset(0, 7).ClearContents
Target.Offset(0, 9).ClearContents
Target.Offset(0, 10).ClearContents
End If
Application.EnableEvents = True
End If
On Error Resume Next
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value = "Galloway 1" Then
Target.Offset(0, 8) = ""
Else
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value <> "Galloway 1" Then
Target.Offset(0, 8) = "N/A"
If Target.Count > 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Application.Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Value = "" Then
Target.Offset(0, 8).ClearContents
If Target.Count > 1 Then Exit Sub
End If
End If
End If
End Sub
```

Solution

Base on the logic of your code, `Change`

event could be simiplified as below.

```
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .CountLarge > 1 Then Exit Sub
If Not .Column = 3 Then Exit Sub
Application.EnableEvents = False
If .Value = "Wind Sites" Or .Value = "Springbok 3" Then
.Offset(0, 2).Resize(1, 4) = "N/A"
.Offset(0, 7).Resize(1, 4) = "N/A"
Else
.Offset(0, 2).Resize(1, 4).ClearContents
.Offset(0, 7).Resize(1, 4).ClearContents
End If
If .Value = "Galloway 1" Or .Value = "" Then
.Offset(0, 8).ClearContents
Else
.Offset(0, 8) = "N/A"
End If
Application.EnableEvents = True
End With
End Sub
```

- 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