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

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
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 :LOL:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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
 
Upvote 0
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. :unsure:

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

Kevin.
 
Upvote 0
Kevin

What exact code did you try?
 
Upvote 0
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
 
Upvote 0
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 ...

Please forgive me. I know nothing about Visual Basic.
Thank you to each of you for your help with this.

I wish I could buy you a round!
:biggrin:
 
Upvote 0
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
 
Upvote 0
You're the man!

Tim ...

Your code works beautifully!
Problem all gone and it appears to work just the same!

Thank you for your expertise and generosity :oops:

- Kevin. :wink:
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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
Back
Top