runtime error '13':

bluefeather8989

Board Regular
Joined
Nov 20, 2009
Messages
249
Office Version
  1. 365
Platform
  1. Windows
Every time I delete a range of cells in G:G i get runtime error '13': The highlighted code is
Target = UCase(Target)
How can I prevent this from happening? Here is the full code

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 7 And Target.Cells.Count = 1 Then
        If Target.Value = "" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -1).Range("A1").Select
            ElseIf Range("Q6").Value = "" Then
                Call Episode1
           
        ElseIf Target.Value = "*" Or Target.Value = "NO" Or Target.Value = "RR" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -2).Range("A1").Select
            Else
                Call Episode1
                ActiveCell.Offset(0, -3).Range("A1").Select
            End If
        End If
    End If
  End If
 
        Dim sReason As String
    Application.EnableEvents = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,556
Office Version
  1. 2013
Platform
  1. Windows
Try this:
I added one new line of code at top of script
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/26/2020  2:01:26 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 7 And Target.Cells.Count = 1 Then
        If Target.Value = "" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -1).Range("A1").Select
            ElseIf Range("Q6").Value = "" Then
                Call Episode1
           
        ElseIf Target.Value = "*" Or Target.Value = "NO" Or Target.Value = "RR" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -2).Range("A1").Select
            Else
                Call Episode1
                ActiveCell.Offset(0, -3).Range("A1").Select
            End If
        End If
    End If
  End If
 
        Dim sReason As String
    Application.EnableEvents = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True
End Sub
 
Solution

bluefeather8989

Board Regular
Joined
Nov 20, 2009
Messages
249
Office Version
  1. 365
Platform
  1. Windows
Try this:
I added one new line of code at top of script
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/26/2020  2:01:26 AM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 7 And Target.Cells.Count = 1 Then
        If Target.Value = "" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -1).Range("A1").Select
            ElseIf Range("Q6").Value = "" Then
                Call Episode1
         
        ElseIf Target.Value = "*" Or Target.Value = "NO" Or Target.Value = "RR" Then
            If Range("Q6").Value = "1" Then
                Call short_Date1
                ActiveCell.Offset(1, -2).Range("A1").Select
            Else
                Call Episode1
                ActiveCell.Offset(0, -3).Range("A1").Select
            End If
        End If
    End If
  End If

        Dim sReason As String
    Application.EnableEvents = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        Target = UCase(Target)
    End If
    Application.EnableEvents = True
End Sub
Thank you, this works great :) Happy Thanks giving and God bless.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,500
Messages
5,636,690
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top