Run Time Error Issue

JessKong1

New Member
Joined
Oct 2, 2016
Messages
1
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

Well-known Member
Joined
May 16, 2015
Messages
858
You could have an infinite loop triggering, try:

Code:
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

Board Regular
Joined
Jun 28, 2016
Messages
192
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.
 

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top