debug message pops up when I select a range of cells and delete

[Guest]

Hello,
When i select a range of cells and hit delete, a that debug message appears.
The range in question is controlled by the following sytax in VB:

If target.Row < 10 Or target.Row > 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) > 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and delete, but
how can I prevent the debug message from appearing.

 

[Chip Pearson]

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

run time error 13
type mismatch

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rob van Gelder]

I suspect an error in a cell.

As an experiment, I entered =1/0 into a cell to give me #DIV/0
Then tried MsgBox Len(Selection)
This gave me Run-time Error 13

You can check for an error:
iserror(selection)

Keep in mind your Target may span many cells, not just one.

 

[keepITcool]

OP doesnt check that Target.Count =1

if the target contains multiple cells:
Len(target) will produce runtime error.
whereas len(target.cells(1)) would not.

normally i'd include
if Target.Count <> 1 then exit sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

 

[Dave Peterson]

This line:
If Len(Target) > 0 Then
is the same as:
If Len(Target.value) > 0 Then

But with multiple cells, target doesn't have a value.

So what should happen when you clean up a multicell range?

One easy solution is to add a check for multiple cells at the top:

if target.cells.count > 1 then exit sub

or you could loop through the cells that are in that area that you're concerned
with:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("H10:H12")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each myCell In Intersect(Target, myRng).Cells
If IsError(myCell.Value) Then
'do nothing??
ElseIf IsNumeric(myCell.Value) = False Then
If Len(myCell.Value) > 2 Then
myCell.Offset(0, -2).Value = ""
End If
End If
Next myCell

Application.EnableEvents = True

End Sub

Personally, I like checking the intersection to see if the change is happening
in a range. I find it easier to read than checking the .row and .column.

 

[Tom Ogilvy]

len(Range("B1:B2"))

produces type mismatch

(len with a multi-cell argument).

If you want to process multiple cells

for each cell in Target
If cell.Row < 10 Or cell.Row > 13 Then Exit Sub
Application.EnableEvents = False
If cell.Column = 8 Then
If IsNumeric(cell) = False Then
If Len(cell) > 0 Then
cell.Offset(0, -2).ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub