# Need help with Run-time error '13": Type mismatch

#### Special-K

##### Board Regular
Ever since I put the following code in my sheet, I get run time errors.

For example, in cells B95 - B102, if I simply delete text in a cell, I get an error message that reads:

Run-time error '13': Type mismatch

The debugger brings up the code below and highlights the second line that starts: If Target.Cells.Count = 1 ... etc.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
Target.Offset(0, 26).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
End If
End Sub

Can somebody help me fix this?
Thank you

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

##### Board Regular
Excel tries to evaluate the entire conditional. It doesn't attempt each piece from left to right.

Generally, you have to arrange your code like so:
Code:
``````If Target.Cells.Count = 1 Then
If Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
Target.Offset(0, 26).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
End If
End If``````

Check to see if you have multiple cells first. You can't perform many operations on a range of cells. You'll get that '13' error.

-Tim

#### Richard Schollar

##### MrExcel MVP
Hi

I expect the problem is in your Ucase statement. Use:

Code:
`` If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target.Value, 1)) = "H" Then``

I think Tim has very valid points from a readibility point of view though.

Best regards

Richard

#### Special-K

##### Board Regular
Still stumped

I tried the code from "tmcfadden" ... but got the following error time and again:

Microsoft Visual Basic
Compile error:
Invalid outside procedure
(OK ... Help)

The word [Target] in the first line is highlighted by the debugger.

- - - - - - - - - - - - - - - - - - - - - - - - - -

I replaced the line of code provided by Richard ... but kept getting the same Run-time errors as before.

Oh ... and these Run-time errors that plague me also pop up when I delete the contents of any cell ... and paste contents into cells. Nearly all the time.

Thank you fellows for your time and help. Don't give up on me. Any other solutions?

Kevin.

#### Norie

##### Well-known Member

Kevin

What exact code did you try?

##### Board Regular
Re: Still stumped

I tried the code from "tmcfadden" ... but got the following error time and again:

Microsoft Visual Basic
Compile error:
Invalid outside procedure
(OK ... Help)

Ya, you have to replace your original IF statement block with mine. That is... you have to put in in the "Private Sub Worksheet_Change(ByVal Target As Range) " routine.

Maybe you put in the global level?

It should look like this:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
Target.Offset(0, 26).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
End If
End If
End Sub``````

-Tim

#### Special-K

##### Board Regular

I tried ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
Target.Offset(0, 26).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
End If
End Sub

-----------
I tried replacing the second line with:
If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target.Value, 1)) = "H" Then
-----------
And I tried:
If Target.Cells.Count = 1 Then
If Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
Target.Offset(0, 26).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
End If
End If
-----------

Would it help to narrow the code down? In other words,
I need the code to watch ONLY cells A5 - A93 for ONLY the following codes: "HC", "HL", "HP", "HE"

Then, prompt with the input box and etc... etc ...

Thank you to each of you for your help with this.

I wish I could buy you a round!

##### Board Regular
Man, I'm at a loss.

The following code works fine for me:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheckOnly As Range
Set rngCheckOnly = Range("A5:A93")

If (Not (Intersect(Target, rngCheckOnly) Is Nothing)) Then
If (Target.Cells.Count = 1) Then
If (UCase(Left(Target, 1)) = "H") Then
Target.Offset(0, 26).Value = Application.InputBox("Enter number of hours", Type:=1)
End If

End If
End If

End Sub``````

You'll notice that I've narrowed down the search by only checking cells A5 through A93. This might make it faster if you do a lot of other stuff in column A outside that range.

I can only think it's something simple that we're missing. You're sure you placing this routine in the right place? It's not in a module, right? It should be under the worksheet that you're working with.

-Tim

#### Special-K

##### Board Regular
You're the man!

Tim ...

Problem all gone and it appears to work just the same!

Thank you for your expertise and generosity

- Kevin. :wink:

Replies
7
Views
342
Replies
6
Views
99
Replies
1
Views
113
Replies
7
Views
148
Replies
3
Views
140

1,136,266
Messages
5,674,725
Members
419,522
Latest member
bizhani

### 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.

### Which adblocker are you using?

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

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