VBA Intersect issue

[bgrice]

Hi. I'm getting an error on the Intersect line of the code below: Object variable or With block variable not set.

The code seems logical to me but I'm a newbie. If the target intersects between C10 and F141 then I want it to perform some checks and amend if necessary. But if it doesn't intersect in that range then I want it to do nothing (exit the sub).

Can someone explain what I'm doing wrong, please? Thanks, Ben

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10:F141")) Then
        If Target.Value = "" Then
            Target.Value = "-"
        ElseIf Target.Value = "0" Then
            Target.Value = "-"
        ElseIf IsEmpty(Target) = True Then
            Target.Value = "-"
        ElseIf IsNumeric(Target) = False Then
            Target.Value = "-"
        End If
        Target.HorizontalAlignment = xlRight
    Else
        Exit Sub
    End If
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C10:F141")) is Nothing Then
        If Target.Value = "" Then
            Target.Value = "-"
        ElseIf Target.Value = "0" Then
            Target.Value = "-"
        ElseIf IsEmpty(Target) = True Then
            Target.Value = "-"
        ElseIf IsNumeric(Target) = False Then
            Target.Value = "-"
        End If
        Target.HorizontalAlignment = xlRight
    Else
        Exit Sub
    End If
End Sub

Although you will get errors if you change more than one cell.

 

[bgrice]

Thanks for your reply, Fluff. I tried this but I get run-time error 28: Out of stack space. And it's the Intersect line that's the offending code. Any suggestions?

 

[Peter_SSs]

Although you will get errors if you change more than one cell.

Another thing is that if any of the conditions are met and the code enters a "-" then
that will re-trigger the worksheet change event code and since the "-" entered is not numeric (one of the conditions) the code will (re) enter a "-" in the cell and
that will re-trigger the worksheet change event code and since the "-" entered is not numeric (one of the conditions) the code will (re) enter a "-" in the cell and
that will re-trigger the worksheet change event code and since the "-" entered is not numeric (one of the conditions) the code will (re) enter a "-" in the cell and
.
.
creating an infinite loop

This is my suggestion, which also deals with the case where multiple cells might be entered at once.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range, Changed As Range
 
  Set Changed = Intersect(Target, Range("C10:F141"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Value = "" Or c.Value = "0" Or IsEmpty(c.Value) Or Not IsNumeric(c.Value) Then c.Value = "-"
    Next c
    Changed.HorizontalAlignment = xlRight
    Application.EnableEvents = True
  End If
End Sub

 

[Peter_SSs]

I get run-time error 28: Out of stack space.

Ah, you just demonstrated my point just as I was posting.

creating an infinite loop

 

[Fluff]

Another thing is that if any of the conditions are met and the code enters a "-" then
that will re-trigger the worksheet change event code and since the "-" entered is not numeric (one of the conditions) the code will (re) enter a "-" in the cell

Oops never really looked at what the code was doing, just the intersect line.
@bgrice try Peter's code, I hadn't noticed the permanent loop.

 

[bgrice]

Thanks Both. Peter's code worked perfectly. Really appreciate your help.

Out of curiosity, why is the code so quick to check each cell in the range? I previously had the below code and was calling this in a worksheet change event but every time a cell changed it took excel 12 seconds to process. Both the code below and the solution use For Each-Next statements but the solution is significantly quicker. Thanks.

Sub WrongValue()
Dim cell As Range
Dim Rng As Range
Set Rng = Range("C10:F141")
For Each cell In Rng
    If cell.Value = "" Then
        cell.Value = "-"
    ElseIf cell.Value = "0" Then
        cell.Value = "-"
    ElseIf IsEmpty(cell) = True Then
        cell.Value = "-"
    ElseIf IsNumeric(cell) = False Then
        cell.Value = "-"
    End If
    Rng.HorizontalAlignment = xlRight
Next
End Sub

 

[Fluff]

It's because every time a cell changes it triggers the change event again, whilst Peter's code stops the event from being triggered again.

 

[bgrice]

Thanks. If you don't mind, can you point to the line of Peter's code that is controlling that, please? Although I think I understand Peter's code, it isn't immediately obvious to me what line is stopping the event from being triggered again.

 

[Fluff]

This part stops all events from being triggered

Application.EnableEvents = False

and then this part re-enables events

Application.EnableEvents = True