In one of the sheet I am developing, there is a range of
cells (D11:F24) which is used to hold user input data.
There are a number of VBA sub routines referred to this
range, which the range address is hardcoded in it.

If the range changes its position whether by cut and paste
or by inserting a new row before it, the address in the
script does not change and cause errors.

I realize it is a bad thing to do. I gave the range a name
and tried to refer to the name of the range in the VBA
script, but failed. Any suggestion on how to get this to
work? I guess, same as other language, it is a bad
practice to "hard code" cell address in the script.

Thanks,
pac


Here is one of the subs

================================================== ===
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Dim myRange As Range

Set myRange = Range("D11:F24") '<< Want to change
"D11:F24" to the name "inputRG"

Set myRange = Intersect(myRange,
myRange.Parent.UsedRange)
If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False
For Each i In myRange
If Not (i.Formula Like "=VLOOKUP($B*" _
And i.Formula Like "=ROUND((VLOOKUP$B*") Then
Sheet19.defaultMan.Value = False
Exit Sub
End If
Next i
Application.EnableEvents = True
End If
End Sub
================================================== =====