HELP - RUN TIME ERROR 13 TYPE MISMATCH - When deleting input from cells

neonerve

New Member
Joined
Jan 30, 2014
Messages
8
Hi there,
New to VBA and the forum. Have a little problem. I have entered some VBA code into my spreadsheet that avoids a CURRENCY PRICE being added to a column if the adjacent column DROPDOWN OPTION does not have a value. It pops up an ERROR MSG and then clears the entered value.

Whenever i try to delete a range of cells more than 1 i get the RUN TIME ERROR 13 TYPE MISMATCH.

Any ideas?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 6) And Target <> "" And (Target.Row < 37 And Target.Row > 6) Then
    If Cells(Target.Row, "e").Value = "" Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        MsgBox "Please select the correct TYPE from the drop down menu"
    End If
End If

If (Target.Column = 8) And Target <> "" And (Target.Row < 37 And Target.Row > 6) Then
    If Cells(Target.Row, "G").Value = "" Then
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        MsgBox "Please select the correct TYPE from the drop down menu"
    End If
End If
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

neonerve

New Member
Joined
Jan 30, 2014
Messages
8
I have formatted all the columns and drop down options that are references in a different sheet to NUMBER and it still occurs
 

neonerve

New Member
Joined
Jan 30, 2014
Messages
8
2 minutes after posting my thread is on page 3... so i assume this may never get answered?
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the Forum!

The error is occurring with the comparison Target <> "" when Target contains more than one cell. Instead, you need to compare each cell within Target. One way would be to adapt your code along the following lines:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngToCheck As Range, cell As Range
    Dim bError As Boolean
    
    Application.EnableEvents = False
     
    Set rngToCheck = Intersect(Target, Range("F6:F37"))
    If Not rngToCheck Is Nothing Then
        For Each cell In rngToCheck
            If cell <> "" And cell.Offset(, -1) = "" Then
                bError = True
                cell.ClearContents
            End If
        Next cell
    End If

    'etc for 2nd part 
    
    If bError Then MsgBox "Please select the correct TYPE from the drop down menu"

    Application.EnableEvents = True

End Sub
 

neonerve

New Member
Joined
Jan 30, 2014
Messages
8
I'm just about to get stuck into this and try it. Before I do. Is there any way to apply the code across multiple checks without copying in 30 times down the page? The cell that it is checking for data is always adjacent to the one with the data entry.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
Is there any way to apply the code across multiple checks without copying in 30 times down the page? The cell that it is checking for data is always adjacent to the one with the data entry.

You could replace this line:

Set rngToCheck = Intersect(Target, Range("F6:F37"))

with

Set rngToCheck = Intersect(Target, Range("F6:F37, H6:H37"))

etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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