I would strongly recommend that you not use ActiveCell in any
calculation, because you cannot predict where the active cell will be,
let alone what worksheet and workbook might be active when Excel
decides that it is time to calculate. If you need to get a reference
to the cell in which the function was called, use either
Application.Caller or Application.ThisCell. These will return a Range
object to points to the cell in which the function was called.

From you use of the word "Target", I'm guessing that you're doing
something with the Change event. Target is a pointer to the cell(s)
that were change. For a Range object, Value is the default property,
so you can omit it, though I think that you should include it. With
that, you can use

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
On Error GoTo ErrH:
If Target.Cells.Count 1 Then
Exit Sub
End If
If Application.Intersect(Target, Range("C_D_Nine")) _
Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Set R = R.Worksheet.Cells(Target.Row, _
Range("C_D_Nime").Column - 1)
R.Value = 1234
ErrH:
Application.EnableEvents = True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Mon, 6 Jul 2009 17:51:01 -0700, Bob Barnes
wrote:

I've read thru Archive Posts here that using "Set t = Target" (below) will
display the value for the Active Cell.

I exchanged ideas earlier today w/ another Forum guy and he got me thinking
on what I need precisely.

After using automation from Access-to-Excel, I have 72 Cells which will look
for the ActiveCell and do a calculation where, for example, the Cell Name of
"CNine" is in the same row as the ActiveCell, but always one column to the
left of the ActiveCell.
...and...
the Cell Name of "C_D_Nine" will always in the same Column of the ActiveCell
and always 2 Rows below the ActiveCell.

How can I change the syntax (below) for all
Range("CNine").Value
...and..
Range("C_D_Nine").Value
...tio something else ?? TIA - Bob

If I can get this, the solution will be found.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set t = Target
If (t - Range("CNine").Value < 0) _
And Abs(t - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = t + I
Else
Range("C_D_Nine").Value = (t - Range("CNine").Value)
End If
End If
End Sub