Issue when updating blank cells automatically

[miss c]

Hi,


I am working on a scheduling calendar tool for my work. While a have a reasonable knowledge of Excel, I know absolutely nothing about VBA. I looked up codes examples for similar needs and have been “frankensteining” my way with some formulas but at this point, I need help. When I look at the code, I get the general idea but I am unable to come up with the specific structure.


I have a section where delivery dates are calculated automatically by formulas placed in hidden columns on the same sheet (same row, 104 columns away.) Since users can input different dates to adjust the time frame when needed, errors can be introduced if a user is deleting the reference to the cell that contains the original formula. I was thinking to avoid this issue by creating a macro that will return the original cell reference (containing the formula) whenever a user is deleting any cell in the range concerned. (Ex: deleting content of cell H6 would return “=DH6”)


This is what I have so far. I did a test with simple formulas and it was working but when I integrate the code in the actual calendar, Excel is crashing. And just before it closes, I get the message “Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed” I assume the segment for the automatic update is somehow incompatible with the code to replace blank cells?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H6:AH9")) Is Nothing Then
    Call UpdateBlank
    End If
    
End Sub




Sub UpdateBlank()
    
    For Each c In Range("H6:AH9")
        If c.Value = "" Then c.Value = "=RC[104]"
    Next
End Sub

 

[jmacleary]

Hi there. I think this version of your UpdateBlank subroutine should work:

Sub UpdateBlank()
    Application.EnableEvents = False
    For Each c In Range("H6:AH9")
        If c.Value = "" Then c.Formula = "=RC[104]"
    Next
    Application.EnableEvents = True
End Sub

 

[miss c]

It works, this is exactly what I needed. I should have asked way sooner and saved myself the trouble... Thank you!