Biff,

If you fill in row 1 with a space or a column title or whatever
it will work...or this might be more flexible:
'----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo StopIt
Dim lngCol As Long
If Target.Address = "$A$2" Then
If Len(Target.Value) Then
For lngCol = 2 To Me.Columns.Count
If Application.CountA(Me.Columns(lngCol)) < (Me.Rows.Count - 1) Then
With Me.Cells(Rows.Count, lngCol).End(xlUp).Offset(1, 0)
If Len(.Value) = 0 Then
.Value = Target.Value
Exit For
End If
End With
End If
Next 'lngCol
End If
End If
Exit Sub

StopIt:
Beep
End Sub
'----------------------------
Regards,
Jim Cone


"Biff" wrote in message ...
Thanks for the reply, Jim. However, this is not working properly.

Once column B is "full" (B2:B65536), then cell B3 is being continually
overwritten.

Let me try to give a better explanation of what I'm trying to do.

Call A2 (although it could be any cell) is updated throughout the day from
another source. This could happen 100's to 1000's of times a day. I want to
capture the history of entries made to cell A2.

The original piece of code worked just fine but I want to expand the history
capability so that when column B is "full" then start writting to column C.
When column C is "full" then start writting to column D. etc., etc.

Thanks!
Biff



"Jim Cone" wrote in message
...
Biff,
'-------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo StopIt
Dim lngCol As Long
If Target.Address = "$A$2" Then
If Len(Target.Value) Then
For lngCol = 2 To Me.Columns.Count
If Application.CountA(Me.Columns(lngCol)) < Me.Rows.Count Then
Me.Cells(Rows.Count, lngCol).End(xlUp).Offset(1, 0).Value =
Target.Value
Exit For
End If
Next
End If
End If
Exit Sub

StopIt:
Beep
End Sub
'------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Biff" wrote in message...
Can this code be modified so that if column B (or whatever column) is
full: =COUNTA(B:B)=65536, then it starts putting the values in the next
column to the right and continues in this fashion?

Actually, it would be =COUNTA(B:B)=65535, since the first target cell is
B2.
Biff

"Biff" wrote in message
...
Hi Folks!

I found this code by Gord Dibben:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
If Target.Address = "$A$2" And Target.Value < "" Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
End Sub

It's event code that "copies" the value entered in A2 to another column
(B2:Bn).

Can this code be modified so that if column B (or whatever column) is
full: =COUNTA(B:B)=65536, then it starts putting the values in the next
column to the right and continues in this fashion?
Thanks!
Biff