VBA if cell value is "" then change another cell value cause type dismatch error

[alantse2010]

Hi all, i am writing the VBA that about change the cell (E19:E24) value if cell D18 value changed and change the cell D18 value if E19: E24 anyone cell value changed.

When the value of D18 is "NA", the value of E19 to E24 is "NA".
When the value of D18 is "C", the value of E19 to E24 is "C".
When the value of E19 to E24 anyone is "NC", the value of D18 is "NC".

Then, it has cause the error "type mismatch" when D18 changed to "NA" or "C".

Anyone can guide me how to fix it?

Thank you very much.

Private Sub Worksheet_Change(ByVal Target As Range)
'If ActiveSheet.Range("D18").Value = "C" Then
'ActiveSheet.Range("E19:E24").Interior.ColorIndex = 8
'
'Else
'ActiveSheet.Range("E19:E24").Interior.ColorIndex = 2
'
'End If
If Not Intersect(Target, Range("D18")) Is Nothing Then
    Select Case Target.Value
    Case "NA"
    Range("E19:E24").Value = "NA"
    Case "C"
    Range("E19:E24").Value = "C"
    
    End Select
    End If
   
If Not Intersect(Target, Range("E19:E24")) Is Nothing Then
  
    Select Case Target.Value
    Case "NC"
     Range("D18").Value = "NC"

    End Select
    End If
End Sub

 

[Peter_SSs]

There are a couple of issues with your code.

1. If D18 is changed & is either NA or C then the code puts values into E19:E24. Doing that triggers the Worksheet_Change code again when that is not required.
To fix that add the blue lines shown below.

2. If multiple cells in E19:E24 are changed at once (eg with Ctrl+Enter entry or Copy/Paste) then Target.Value will cause an error because there is more than one target cell.
What are you trying to achieve with the second part of the code?
What should happen if "NC' is entered into E20 and "X" is entered into E21 at the same time? Or do you expect that only one cell in E19:E24 would be changed at a time?

If Not Intersect(Target, Range("D18")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Value
    Case "NA"
    Range("E19:E24").Value = "NA"
    Case "C"
    Range("E19:E24").Value = "C"
    
    End Select
    Application.EnableEvents = True
End If

 

[Peter_SSs]

BTW, that first section of code could also be written like this

If Not Intersect(Target, Range("D18")) Is Nothing Then
  If Range("D18").Value = "NA" Or Range("D18").Value = "C" Then
    Application.EnableEvents = False
    Range("E19:E24").Value = Range("D18").Value
    Application.EnableEvents = True
  End If
End If

 

[bebo021999]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("D18")) Is Nothing Then
    If Not Intersect(Target, Range("E19:E24")) Is Nothing Then
        If Target.Value = "NC" Then Range("D18").Value = "NC"
    End If
Else
        Select Case Target.Value
            Case "NA"
                Range("E19:E24").Value = "NA"
            Case "C"
                Range("E19:E24").Value = "C"
        End Select
End If
Application.EnableEvents = True
End Sub

 

[alantse2010]

There are a couple of issues with your code.

1. If D18 is changed & is either NA or C then the code puts values into E19:E24. Doing that triggers the Worksheet_Change code again when that is not required.
To fix that add the blue lines shown below.

2. If multiple cells in E19:E24 are changed at once (eg with Ctrl+Enter entry or Copy/Paste) then Target.Value will cause an error because there is more than one target cell.
What are you trying to achieve with the second part of the code?
What should happen if "NC' is entered into E20 and "X" is entered into E21 at the same time? Or do you expect that only one cell in E19:E24 would be changed at a time?

If Not Intersect(Target, Range("D18")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Value
    Case "NA"
    Range("E19:E24").Value = "NA"
    Case "C"
    Range("E19:E24").Value = "C"
   
    End Select
    Application.EnableEvents = True
End If

Thank you very much for your reply and guidance.
It is noted that the cause of the error.
If "NC' is entered into E20 and "X" is entered into E21 at the same time, it should the value of D18 changed.
I just want to only one cell in E19:E24 would be changed at a time.
Thank you.

 

[alantse2010]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("D18")) Is Nothing Then
    If Not Intersect(Target, Range("E19:E24")) Is Nothing Then
        If Target.Value = "NC" Then Range("D18").Value = "NC"
    End If
Else
        Select Case Target.Value
            Case "NA"
                Range("E19:E24").Value = "NA"
            Case "C"
                Range("E19:E24").Value = "C"
        End Select
End If
Application.EnableEvents = True
End Sub

It works perfectly, thank you very much for your help.

 

[Peter_SSs]

If "NC' is entered into E20 and "X" is entered into E21 at the same time, it should the value of D18 changed.

I didn't understand that. What does it mean?

I just want to only one cell in E19:E24 would be changed at a time.

Just the same, if say two cells in that range, or perhaps all the cells, are selected and, say, the Delete key is pressed then
a) The code will error
b) Most likely you will be left with events disabled so that any further changes to the relevant cells will not be acted on (until events are re-enabled or Excel is restarted).

I would go for something a bit more robust.
Assuming you only expect one cell to be changed at a time ..

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    Application.EnableEvents = False
    If Not Intersect(Target, Range("D18")) Is Nothing Then
      If Target.Value = "NA" Or Target.Value = "C" Then Range("E19:E24").Value = Target.Value
    Else
      If Not Intersect(Target, Range("E19:E24")) Is Nothing And Target.Value = "NC" Then Range("D18").Value = "NC"
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[alantse2010]

I didn't understand that. What does it mean?



Just the same, if say two cells in that range, or perhaps all the cells, are selected and, say, the Delete key is pressed then
a) The code will error
b) Most likely you will be left with events disabled so that any further changes to the relevant cells will not be acted on (until events are re-enabled or Excel is restarted).

I would go for something a bit more robust.
Assuming you only expect one cell to be changed at a time ..

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    Application.EnableEvents = False
    If Not Intersect(Target, Range("D18")) Is Nothing Then
      If Target.Value = "NA" Or Target.Value = "C" Then Range("E19:E24").Value = Target.Value
    Else
      If Not Intersect(Target, Range("E19:E24")) Is Nothing And Target.Value = "NC" Then Range("D18").Value = "NC"
    End If
    Application.EnableEvents = True
  End If
End Sub

I didn't understand that. What does it mean?
It means if "NC' is entered into E20 and "X" is entered into E21 at the same time, the value of D18 will change to "NC".

OK, i know what you mean, thank you very much for your guidance.
It works perfectly, thank you

 

[Peter_SSs]

It means if "NC' is entered into E20 and "X" is entered into E21 at the same time ..

I take it that is not actually going to happen then?

OK, i know what you mean, thank you very much for your guidance.
It works perfectly, thank you

Cheers.

 

[alantse2010]

I take it that is not actually going to happen then?


Cheers.

I take it that is not actually going to happen then?
Fine, that's OK, no need to care.
It can close file.