| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one column is full, goto the next column
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If one column is full, goto the next column
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 "Biff" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| matching full name to 'two column' name using sumproduct | Excel Worksheet Functions | |||
| add data in column related to cell in another column - see full de | Excel Worksheet Functions | |||
| Full Text is not appearing in column | Excel Discussion (Misc queries) | |||
| How I can print full text bigger than column, in repeat column | Excel Discussion (Misc queries) | |||
| How to specify almost full-column arguments like A3:A | Excel Discussion (Misc queries) | |||
Linear Mode
