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

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.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Apr 18, 2006
Messages
63
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Kevin

What exact code did you try?
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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
Joined
Apr 18, 2006
Messages
63

ADVERTISEMENT

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:
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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
Joined
Apr 18, 2006
Messages
63
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:
 

Forum statistics

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