Clear Contents of cell A when contents of cell B changes - VBA

[spydey]

I have some code that I currently use where if cell A changes, then cell B adjusts accordingly. If cell A is cleared, then cell B is cleared too.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 7 Then
        If UCase(Target.Value) = "PASS" Or UCase(Target.Value) = "FAIL" Then
            Target.Offset(0, 1) = Date
        Else
            Target.Offset(0, 1).ClearContents
        End If
    End If


End Sub

Works great!

However, I need to use something similar for a different project. So I adjusted the code as needed. But now, when the contents of cell A are cleared, cell B does not clear out. I have tried a few different approaches and I don't seem to get the correct results.

What is wrong with my code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 5 Then
        If IsNumeric(Target.Value) = True Then
            Target.Offset(0, -3) = Date
        Else
            Target.Offset(0, -3).ClearContents
        End If
    End If


End Sub

I also tried this, but to no avail:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    If Target.Count = 1 And Target.Column = 5 Then
        If IsNumeric(Target.Value) = True Then
            Target.Offset(0, -3) = Date
        ElseIf IsEmpty(Target.Value) = True Then
            Target.Offset(0, -3).ClearContents
        End If
    End If


End Sub

Any thoughts? I bet it is something very obvious!! hahahah lol

-Spydey

P.S. I noticed that with my original code, I could paste the PASS or FAIL into the cells, and the dates would change, as needed. However, with pasting the numbers into this new project, the date doesn't change. For this new project, the value in column 5 will always be numeric (always numbers).

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
    If Target.CountLarge = 1 And Target.column = 5 Then
      If Len(Target.Value) = 0 Then
         Target.Offset(0, -3).ClearContents
      ElseIf IsNumeric(Target.Value) = True Then
         Target.Offset(0, -3) = Date
      End If
    End If
Application.EnableEvents = True

End Sub

 

[mumps]

This code worked for me.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Count = 1 And Target.Column = 5 Then
        If IsNumeric(Target.Value) = True Then
            Target.Offset(0, -3) = Date
        Else
            Target.Offset(0, -3).ClearContents
        End If
    End If
End Sub

You say

when the contents of cell A are cleared, cell B does not clear out

you are referencing column A when your target range in the macro is column 5 which is column E not column A.

 

[Fluff]

IsNumeric will return TRUE for an empty cell as it can be evaluated as 0.
Therefore when the contents of a cell are deleted, isnumeric will still return true, so you never get to the Else statement

Have a look at post#2 here, for more info on the problems of isnumeric
https://www.mrexcel.com/forum/excel...esults-isnumeric-vba-funtion.html#post3736318

 

[spydey]

You say you are referencing column A when your target range in the macro is column 5 which is column E not column A.

So my code is actually referencing column 5, which is "E", and then 3 back from E, which is column B. In my OP I was stating cell A and cell B, not as the actual columns but as just a reference to when something changes "here" then a change should happen "there". I wasn't actually meaning column A and column B.

Hopefully I understood your statement correctly and clarified it with mine. :D

-Spydey

 

[spydey]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
    If Target.CountLarge = 1 And Target.column = 5 Then
      If Len(Target.Value) = 0 Then
         Target.Offset(0, -3).ClearContents
      ElseIf IsNumeric(Target.Value) = True Then
         Target.Offset(0, -3) = Date
      End If
    End If
Application.EnableEvents = True

End Sub

I will give this a quick try and report back.

Also, interesting about the problems regarding IsNumeric. I was not aware of them. And I guess it makes sense that a blank cell could be evaluated as "0". I hadn't thought of that. So really, my code, concept, was correct, but it was just the use of a parameter that evaluates blanks as 0 which was giving me the incorrect output. I will have to remember that distinction for next time.

-Spydey

 

[spydey]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
    If Target.CountLarge = 1 And Target.column = 5 Then
      If Len(Target.Value) = 0 Then
         Target.Offset(0, -3).ClearContents
      ElseIf IsNumeric(Target.Value) = True Then
         Target.Offset(0, -3) = Date
      End If
    End If
Application.EnableEvents = True

End Sub

Question: Why change the "If Target.Count" to "If Target.CountLarge"? Just curious .....

-Spydey

 

[Fluff]

Question: Why change the "If Target.Count" to "If Target.CountLarge"? Just curious .....

-Spydey

If somebody selected all the cells & tried to delete them, .Count would give you an overflow error. Have a look here for a full explanation
https://msdn.microsoft.com/en-us/vb...nt-property-excel?f=255&MSPPError=-2147217396

 

[spydey]

If somebody selected all the cells & tried to delete them, .Count would give you an overflow error. Have a look here for a full explanation
https://msdn.microsoft.com/en-us/vb...nt-property-excel?f=255&MSPPError=-2147217396

I actually just found that a moment ago as I was investigating. I was going to post my response but you beat me to it. lol

Thanks for the info.

-Spydey

 

[spydey]

Try

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.EnableEvents = False
    If Target.CountLarge = 1 And Target.column = 5 Then
      If Len(Target.Value) = 0 Then
         Target.Offset(0, -3).ClearContents
      ElseIf IsNumeric(Target.Value) = True Then
         Target.Offset(0, -3) = Date
      End If
    End If
Application.EnableEvents = True

End Sub

Ok, so for some reason, it doesn't want to add the date, or clear our the date.

I tried it on a blank, new, fresh workbook, and it works fine. So there is something with my current project workbook that is causing it to not update the correct cell .... I will investigate more and report back.

Thanks for your help so far Fluff!

-Spydey