FracinDean
Board Regular
- Joined
- Jul 11, 2011
- Messages
- 110
- Office Version
- 365
- Platform
- Windows
My objective is to use the Worksheet_Change event to test if any cells requiring a value have been cleared, and then to retrieve the default values and put them in the cell. All the cells requiring a value have a named range, and all the default named ranges are the same, except with the letters "Df" appended on them. Example, a named range "Item1" has its default value in a range named "Item1Df". All of my named ranges have scope = "Workbook". I am getting a range object failure, 1004, in the line marked "Error occurs here".
Code:
If Not Intersect(Target, Range("cellsWithDefaultValues")) Is Nothing Then
If Target.Value = "" Then
Dim targetName As Variant
targetName = Target.Name.Name
MsgBox (targetName & "Df") 'Everything is okay up to here
MsgBox Range(targetName & "Df").Value 'Error 1004 occurs here
Target.Value = Range(targetName & "Df").Value 'This is what I eventually want to do
End If
End If