none
Send info on a row to new row after finding the next empty cell of a row. RRS feed

  • Question

  • Hi,

    I have a sheet that has info in columns B, C, D, E, F and buttons in each row of column A. There is always info in column "B" but not always in the other columns of that row. I want to click a button to send the info in the row the button is associated with to the next row with an empty cell in column "J" so that if row 7 of column "J" is the next empty cell of that column then the info from B, C, D, E, F of the row associated with the button will be put into cells J7, K7, L7, M7, N7 even if some of the cells in the other columns are empty.

    I have tried using this code but it puts the info in the next empty cell of Each column and the info gets separated:

    Sub a11AddToNextCell()
    x = Cells(Rows.Count, "J").End(xlUp).Row
    nar = Range("J1:J260" & x).Find("").Row
    Cells(nar, "J").Value = Range("B11").Value
    x = Cells(Rows.Count, "K").End(xlUp).Row
    nar = Range("K1:K260" & x).Find("").Row
    Cells(nar, "K").Value = Range("C11").Value
    x = Cells(Rows.Count, "L").End(xlUp).Row
    nar = Range("L1:L260" & x).Find("").Row
    Cells(nar, "L").Value = Range("D11").Value
    x = Cells(Rows.Count, "M").End(xlUp).Row
    nar = Range("M1:M260" & x).Find("").Row
    Cells(nar, "M").Value = Range("E11").Value
    x = Cells(Rows.Count, "N").End(xlUp).Row
    nar = Range("N1:N260" & x).Find("").Row
    Cells(nar, "N").Value = Range("F11").Value
    End Sub

    That works fine for individual cells but I need the Row of info to be posted to the same row so that the info on that row is not separated. 

    Please help a novice,

    Thanks

    FoosMaster

    Thursday, December 15, 2016 10:47 PM

Answers

  • I ended up coming up with this:

    Sub Paste_to_Next007()
        Range("B7:F7").Select
        Selection.Copy
        x = Cells(Rows.Count, "J").End(xlUp).Row
        nar = Range("J1:J500" & x).Find("").Row
        Cells(nar, "J").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("G7").Delete
        Range("B7").Select
    End Sub

    *(I use "Delete" in empty column/cell G7 to stop the copy mode. Yeah, I'm sure there is a better way that this beginner doesn't know.)

    Master Foo

    • Marked as answer by FoosMaster Monday, December 19, 2016 3:01 AM
    Monday, December 19, 2016 3:01 AM

All replies

  • Hi FoosMaster,

    First try to get the range of the clicked button.

    then try to use offset to get the value of "J7".

    if value is empty then try to copy the range of (B7:F7).

    here try to use "Address" with that you can get row and column and copy the range.

    then again using offset try to paste the range value that you copied before.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 16, 2016 1:46 AM
    Moderator
  • I really don't know how to do that and don't know what you mean by offset or "Address". I guess I was being to generous with the term Novice and should have used Beginner. 

    Note: Each row has a button in column "A" and the info beside that button in columns "B-C-D-E-F" is to be sent to columns J-K-L-M-N from random rows. Not all columns of that row will always have information except for column B. (B25 will always have info but C25-D25-E25-F25 may or may not have info)

    For instance: If "J9" is the next empty cell in column "J" and someone presses the button in cell A25 then the info in cells B25-C25-D25-E25-F25 should be sent to J9-K9-L9-M9-N9 respectively. 

    I tried modifying the formula I have above by using: 

    Sub a025AddToNextCell()
        x = Cells(Rows.Count, "J").End(xlUp).Row
        nar = Range("J1:J260" & x).Find("").Row
        Cells(nar, "J").Value = Range("B25,C25,D25,E25,F25").Value
    End Sub


    But that doesn't work and the other formula looks for the next empty cell in Each column and splits the info up. I just really don't know much about VBA.


    • Edited by FoosMaster Friday, December 16, 2016 3:27 AM
    Friday, December 16, 2016 2:36 AM
  • I ended up coming up with this:

    Sub Paste_to_Next007()
        Range("B7:F7").Select
        Selection.Copy
        x = Cells(Rows.Count, "J").End(xlUp).Row
        nar = Range("J1:J500" & x).Find("").Row
        Cells(nar, "J").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("G7").Delete
        Range("B7").Select
    End Sub

    *(I use "Delete" in empty column/cell G7 to stop the copy mode. Yeah, I'm sure there is a better way that this beginner doesn't know.)

    Master Foo

    • Marked as answer by FoosMaster Monday, December 19, 2016 3:01 AM
    Monday, December 19, 2016 3:01 AM