I am trying to construct a spreadsheet in Excel 2003 where a sequence of
results are entered into a column as each event occurs. Each new result
(value) entered into the next cell of the column can be one of several
different types and the number & type of these results are recorded using
COUNT in another area of the sheet.
I want to enter the value returned by COUNT in the cell adjacent to last
result entered without it changing to the new value of COUNT when the next
result is entered into the column of results.
The formula in the adjacent cell (=value of cell returned by COUNT) can be
replaced by its value by a simple macro that copies itself then" paste
special_value" but can I make this macro run each time I leave the last
result cell in the result column?

Dave

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
mycount = Range("F19").Value
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
Excel.Range("B" & n).Value = mycount
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Will copy the current value of F19 into Bn


Gord Dibben Excel MVP

On Sat, 3 Dec 2005 09:43:02 -0800, "Dave Lister" <Dave
wrote:

I am trying to construct a spreadsheet in Excel 2003 where a sequence of
results are entered into a column as each event occurs. Each new result
(value) entered into the next cell of the column can be one of several
different types and the number & type of these results are recorded using
COUNT in another area of the sheet.
I want to enter the value returned by COUNT in the cell adjacent to last
result entered without it changing to the new value of COUNT when the next
result is entered into the column of results.
The formula in the adjacent cell (=value of cell returned by COUNT) can be
replaced by its value by a simple macro that copies itself then" paste
special_value" but can I make this macro run each time I leave the last
result cell in the result column?