Why clearing excel cell in vba do not work correctly?

Question

Excel 2003 (on others not tested)

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Target
       If Not Intersect(cell, Range("A2:A100")) Is Nothing Then
            cell.ClearContents
            cell.Offset(0, 1).Value = CInt(cell.Offset(0, 1).Value) + 1
       End If
    Next cell
End Sub

if delete string

cell.ClearContents

it's work fine, next sibling cell increments,

but with it next sibling cell value increases by several hundred

Why?

How to do this job correctly?

Try `application.EnableEvents = False` first thing when you enter the `Worksheet_Change` `Sub`, otherwise it will be invoked recursively every time you change the sheet from actions within `Worksheet_Change`. At the end of the subroutine (preferably also using an error handler) revert `Application.EnableEvents= True`. [This](http://www.cpearson.com/excel/Events.aspx) might help. — Ioannis, May 21 '15 at 01:17
It 'works' without the `cell.ClearContents` because even though the *Worksheet_Change* is called again and runs on top of itself, the second time *Target* is in column B and it doesn't meet your `Intersect`. Still wrong, but it does run. Not recommended. Toggle `application.EnableEvents` as recommended above. — , May 21 '15 at 01:52
It is better if you fully qualify your `Range("A2:A100")`, see point 3 [here](http://stackoverflow.com/a/30363620/2707864) (only as an example of the countless times this shows up). Unless you explicitly do not want to, in which case you better implement [this](http://stackoverflow.com/a/27838913/2707864). — sancho.s ReinstateMonicaCellio, May 21 '15 at 02:53
@Ioannis - thanks for your comments and links, it's work fine — jah, May 21 '15 at 12:24

score 1 · Answer 1 · answered May 21 '15 at 08:01

Add Option Explicit to protect your vba from having undeclared variables and to eliminate the possibilities of Compile Error

Try this way it should work

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Variant
    For Each cell In Target
       If Not Intersect(cell, Range("A2:A100")) Is Nothing Then
            cell.ClearContents
            cell.Offset(0, 1).Value = CInt(cell.Offset(0, 1).Value) + 1
       End If
    Next cell
End Sub

thanks for the clarification, but your version also gives the same result as me, cell value increases by several hundred, not by one — jah, May 21 '15 at 12:28

Why clearing excel cell in vba do not work correctly?

1 Answers1