+ Reply to Thread
Results 1 to 7 of 7

If one column is full, goto the next column

  1. #1
    Biff
    Guest

    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. #2
    Biff
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Jim Cone
    Guest

    Re: 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" <[email protected]> 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Biff
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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. #5
    Jim Cone
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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. #6
    Biff
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> 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. #7
    Jim Cone
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1