Worksheet_change Error 91

[michaelg777]

First time writing worksheet_change code and I have gotten 2 codes to work, but I cannot get them to work together. The first piece of code is just to hide and unhide tabs, and it works until I paste the second piece of code which is to replace cell values when entered into a specific range. Also I get the error when I type one of the values outside of the defined range. Could someone please assist with what I am missing in my code to have both work at the same time?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E1")) Is Nothing Then
Application.ScreenUpdating = False

Sheets("3 Year - Output").Visible = xlVeryHidden
Sheets("4 Year - Output").Visible = xlVeryHidden
Sheets("5 Year - Output").Visible = xlVeryHidden

Select Case Target.Value
Case "3 Year Output"
Sheets("3 Year - Output").Visible = True
Case "4 Year Output"
Sheets("4 Year - Output").Visible = True
Case "5 Year Output"
Sheets("5 Year - Output").Visible = True
Case Else
'All sheets remain hidden
End Select
Application.ScreenUpdating = True
End If

Dim rng2 As Range
Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
If rng2 = "0" Then
rng2.Replace what:="0", Replacement:="New York"
End If
If rng2 = "1" Then
rng2.Replace what:="1", Replacement:="New Jersey"
End If
"
End If
End Sub

 

[michaelg777]

Thanks! That worked! My only issue now though is that if the data is pasted it doesnt automatically make the change unless I click into the cell and press enter, is there a way to fix that?

+1


I think the problem is that you're not verifying that the cell which changed is within your specified range
The line I highlighted red does NOT actually verify if Target exists within Range("$F$4:$G$100000")
In reality, it basically says...
If Target does exist within Range("$F$4:$G$100000"), then set rng2 to be that intersecting cell.
Otherwise, rng2 remains to be a range object variable without a range assigned to it.

Dim rng2 As Range
 Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
 If rng2 = "0" Then
 rng2.Replace what:="0", Replacement:="New York"
 End If
 If rng2 = "1" Then
 rng2.Replace what:="1", Replacement:="New Jersey"
 End If

You have to do an If Not Intersect(...) Is Nothing
Like you did in the first part of the code.


In this case, I'd do it this way.

Dim rng2 As Range
 Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
 If Not rng2 Is Nothing Then
     If rng2 = "0" Then
         rng2.Replace what:="0", Replacement:="New York"
     End If
     If rng2 = "1" Then
         rng2.Replace what:="1", Replacement:="New Jersey"
     End If
 End If

 

[Comfy]

Probably mixed that with the rule
"A Custom Function (UDF) cannot change values in cells if that function is called from a worksheet formula"

That's exactly it..... Doh!

 

[Jonmo1]

I take it when you Paste, you're pasting more than one cell at a time..

This line makes the macro stop when you do that.
If Target.Count > 1 Then Exit Sub

I would re-arrange the code so the 2nd part comes first.
And write it this way so it loops through all the cells.

Dim rng2 As Range, c As Range
 Set rng2 = Intersect(Target, Range("$F$4:$G$100000"))
 If Not rng2 Is Nothing Then
     For Each c In rng2
         If c = "0" Then
             c.Replace what:="0", Replacement:="New York"
         End If
         If c = "1" Then
             c.Replace what:="1", Replacement:="New Jersey"
         End If
     Next c
 End If

 

[michaelg777]

Thanks so much that worked! It goes pretty slow though with making the changes, any way to make it faster?

 

[Jonmo1]

Turn off Calculations and Events at the beginning, then back on at the end...

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

'rest of code here

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

 

[michaelg777]

Works much better now! Thanks!

 

[Jonmo1]

You're welcome.