Run-time error '13': Type mismatch

alno00

New Member
Joined
Mar 15, 2018
Messages
3
Hello everybody!
I got some amazing help last time I posted, so I'll give it another go.
I'm still very new to coding in excel and find myself stuck with a Run-time error yet again.

This is the code I'm working with:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long

If Not Intersect(Target, Range("G:J")) Is Nothing Then

    If InStr(1, Target, "SLT003", vbTextCompare) Then
        Target.Offset(0, -4) = "reply 1"
            Else
            Target.Offset(0, -4) = ""
    End If
    
    If InStr(1, Target, "SLT004", vbTextCompare) Then
        Target.Offset(0, -4) = "Reply 1"
    End If

End If
End Sub
Basically, when I enter an SLT-value into a cell, I want the cell 4 steps to the left to automatically enter a reply. The reply should also disappear if the SLT-value is removed.

The code generally works as intended but if I delete rows, enter rows or try to drag fill my SLT value into more cells I recieve a "Run-time error '13': Type mismatch" message.

I'm sure there are many things that could be improved with my code. Any takers? :(

Best regards
/Alexander
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,094
Office Version
365
Platform
Windows
Try it like this perhaps:

Code:
Dim rng As Range, c As Range

Set rng = Intersect(Target, Range("G:J"))

If Not rng Is Nothing Then
    For Each c In rng
        If InStr(1, c, "SLT003", vbTextCompare) + InStr(1, c, "SLT004", vbTextCompare) Then
            c.Offset(0, -4) = "reply 1"
        Else
            c.Offset(0, -4) = ""
        End If
    Next
End If
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,933
Messages
5,508,196
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top