Run Time Error Issue

[JessKong1]

Hi!

I used the code below in a hidden worksheet so that if text is entered into a template and then deleted, the formulas will reappear. Sometimes, but not always, I receive the following error.

Run-time error '-2147417848 (80010108)' Method 'Formula' of object 'Range failed

This is the code that I am currently using.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("D5:I26")) Is Nothing Then Exit Sub
If Target = "" Then
Target.Formula = Sheets("Saturday Hidden").Cells(Target.Row, Target.Column).Formula
End If

End Sub

The error is occurring at this point [FONT=&quot]Target.Formula = Sheets("Saturday Hidden").Cells(Target.Row, Target.Column).Formula[/FONT]

Can someone please advise on what needs to be modified in order for the macro to work properly?</code>

 

[RedBeard]

You could have an infinite loop triggering, try:

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("D5:I26")) Is Nothing Then Exit Sub
If Target = "" Then
Target.Formula = Sheets("Saturday Hidden").Cells(Target.Row, Target.Column).Formula
End If


Application.EnableEvents = True


End Sub

 

[NotoriousPopol]

You should also verify is the cell in sheets("Saturday Hidden") has a formula thanks to the function HasFormula, before you "copy/paste" this formula.

 

[RedBeard]

You should also verify is the cell in sheets("Saturday Hidden") has a formula thanks to the function HasFormula, before you "copy/paste" this formula.

Good point.