If one column is full, goto the next column

[Biff]

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

 

[Biff]

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

 

[Jim Cone]

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

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]

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]

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


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

 

[Biff]

Thanks, Jim!

This modified code works.

What's the Beep for? Is that the after On Error action? (for the most part,
I'm VBA "challenged")

Biff

 

[Jim Cone]

Biff,
You are welcome.
The beep only beeps if an error occurs.
That's better then not knowing.
I think we are all VBA challenged at one time or another.
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Thanks, Jim!

This modified code works.
What's the Beep for?
Is that the after On Error action?
(for the most part, I'm VBA "challenged")
Biff