VBA Target.value

[Victor]

Hi:

Please need help with the "Target.value = 0" IF to clear the cell contents in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 0 Then
   Target.Offset.Offset(0, 1).ClearContents
End If
If Target.Column = 1 Then
   If Target.Row > 10 Then
      If Target.Row < 15 Then
         Application.EnableEvents = False
         Target.Offset.Offset(0, 1) = Now()
         Application.EnableEvents = True
      End If
   End If
End If
End Sub

Thanks for the help.

Victor

[lucas]

Could this be the problem. You have:

Target.Offset.Offset(0, 1).ClearContents

Try:

Target.Offset(0, 1).ClearContents

[Victor]

Lucas:

I did the correction suggested but I still can not run the IF section:

If Target.Value = 0 Then
   Target.Offset(0, 1).ClearContents
     End If

See corrected vba code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = 0 Then
   Target.Offset(0, 1).ClearContents
     End If
    If Target.Column = 1 Then
   If Target.Row > 10 Then
      If Target.Row < 15 Then
         Application.EnableEvents = False
         Target.Offset.Offset(0, 1) = Now()
         Application.EnableEvents = True
      End If
   End If
    End If
End Sub

Thanks for the suggestion.

Victor

[tpoynton]

the first if statement works for me, if I put a 0 in column A, anything in column B in the same row is cleared. Perhaps you need to exit the sub if the value is 0? try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = 0 Then
   Target.Offset(0, 1).ClearContents
   Exit Sub
    End If
    If Target.Column = 1 Then
   If Target.Row > 10 Then
      If Target.Row < 15 Then
         Application.EnableEvents = False
         Target.Offset(0, 1) = Now()
         Application.EnableEvents = True
      End If
   End If
    End If
End Sub

[Bob Phillips]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Value = 0 Then
   Target.Offset(0, 1).ClearContents
    End If
If Not Intersect(Target, Me.Range("A11:A14")) Is Nothing Then
   Target.Offset(0, 1) = Now()
    End If
ws_exit:
    Application.EnableEvents = True
End Sub

[Victor]

Hi:

I am enclosed file with the code. May be there you can catch the problem.

Thanks for the help.

Victor

[Bob Phillips]

My code supplied earlier works fine with that workbook. Why didn't you try it?

[Victor]

XLD:

I just not want a solution but to learn what is the problem with the code that apparently should work.

I really appreciate your help.

Thanks.

Victor

[mdmackillop]

You need to set EnableEvents to False before the code makes any changes

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Value = 0 Then
   Target.Offset.Offset(0, 1).ClearContents
End If
If Target.Column = 1 Then
   If Target.Row > 10 Then
      If Target.Row < 15 Then
         Target.Offset(0, 1) = Now()
      End If
   End If
End If
Exits:
Application.EnableEvents = True
End Sub

[Bob Phillips]

XLD:

I just not want a solution but to learn what is the problem with the code that apparently should work.

I really appreciate your help.

Thanks.

Victor

Then why didn't you say that rather than posting a workbook with the same code that you posted earlier.

When you change a value to 0 it just generates an endless cascade of changes. because you change the value to the right, which triggers the event again, and so on.

[Victor]

mdmackillop and xlt:

I tried both xld code and now mdmackillop and when I run the code with Excel 2003 the code do not erase the offset(0,1) contents when the target.value = 0.

I posted the file so you can run it and check it out to see if it runs.

I am sorry if I do not explained myself clearly, it was not my intention, but still I really need your help as to why the suggested code do no run the clearcontents code.

Thanks for the help.

Victor

[Victor]

Thanks to all:

If I add Goto Exits after the instruction below solves my problem for the codes suggested by mdmackillop and xlt:

Target.Offset.Offset(0, 1).ClearContents 
Goto Exits

Without this intruction, in both suggested codes it shows the date (now()) when clear the cells contents. Which was my problem inicially.

Thanks to all for the help.

Victor

[Bob Phillips]

Better to use

Private Sub Worksheet_Change(ByVal Target As Range) 
On Error Goto ws_exit 
Application.EnableEvents = False 
If Target.Value = 0 Then 
   Target.Offset(0, 1).ClearContents 
   ElseIf Not Intersect(Target, Me.Range("A11:A14")) Is Nothing Then 
   Target.Offset(0, 1) = Now() 
    End If 
ws_exit: 
    Application.EnableEvents = True 
End Sub

than to use Goto

[mdmackillop]

Hi Victor,
Stepping through your original code would show the events occuring and allow you to see where the code was going wrong.
Regards
MD

[Victor]

Another option is the code modified by tpoynton adding:

Target.Offset(0, 1).ClearContents 
 Exit Sub

Thanks all for your interest is providing different alternatives to solved the problem but the one provided by xld required less steps.

Victor