Designated a cell as a name range

[Fastbike]

Can I designate the cell that I am is a "named range"?
For exmample:

Range("A1").Select
Cells.Find(What:="Involuntary Termination Count", MatchCase:=True).Select
Selection.ClearContents
Selection.Offset(1, 0).Select

How do I program VBA to name the active cell I am in?

(I need to program this way as Involuntary Termination Count keeps moving depending on the pivot table report generated.

Thanks.

[Rowan]

Without actually selecting any cells:

Dim InvTC As Range
Dim myRange As Range
With Cells
Set InvTC = .Find(What:="Involuntary Termination Count" _
, MatchCase:=True)
End With
If Not InvTC Is Nothing Then 'checks that Involuntary Ter... was found
InvTC.ClearContents
Set myRange = InvTC.Offset(1, 0)
ActiveWorkbook.Names.Add Name:="myNamedRange", _
RefersTo:="=" & ActiveSheet.Name & "!" & myRange.Address
End If

Hope this helps
Rowan

"Fastbike" wrote:

>
> Can I designate the cell that I am is a "named range"?
> For exmample:
>
> Range("A1").Select
> Cells.Find(What:="Involuntary Termination Count",
> MatchCase:=True).Select
> Selection.ClearContents
> Selection.Offset(1, 0).Select
>
> How do I program VBA to name the active cell I am in?
>
> (I need to program this way as Involuntary Termination Count keeps
> moving depending on the pivot table report generated.
>
> Thanks.
>
>
> --
> Fastbike
> ------------------------------------------------------------------------
> Fastbike's Profile: http://www.excelforum.com/member.php...o&userid=26701
> View this thread: http://www.excelforum.com/showthread...hreadid=400318
>
>

[Fastbike]

Great, I will try that out.

Thanks for the help