none
Find first empty cell of a column and paste specific range Only if not a duplicate. RRS feed

  • Question

  • I have an excel database and am using a VBA to find the next empty cell of a column then paste a range to that. I need to be able to do the same I am doing but Without pasting if it is a Duplicate and a message box that states "Duplicate".

    I have a button in column "A" of rows 3 thru 260 with similar code for each button so that pressing the button on row "7" uses this code:

    Sub End_of_List007()
        Range("B7:D7").Select
        Selection.Copy
        x = Cells(Rows.Count, "J").End(xlUp).Row
        nar = Range("J1:J260" & 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" on empty column/cell "G7" to stop the "copy" selection, there is probably a better way that this beginner is just not aware of)

    I just need to be able to stop the Duplicate paste to column "J" if it is a Duplicate name. (With an alert message would be Great but not necessary.)

    Thanks,

    Master Foo






    Monday, December 19, 2016 2:34 AM

Answers

  • Hi,

    If you don’t want to use Copy/Paste, you could use formula to reference the cells("B7")

    Sub End_of_List007()
    Dim rng As Range
    Set rng = Range("J:J")
    rsl = WorksheetFunction.CountIf(rng, Range("B7"))
    If rsl = 0 Then
    x = Cells(Rows.Count, "J").End(xlUp).Row
    nar = Range("J1:J260" & x).Find("").Row
    Cells(nar, "J").Formula = "=B7"
    Cells(nar, "J").Select
    Selection.AutoFill Destination:=Range(Cells(nar, "J"), Cells(nar, "L")), Type:=xlFillDefault
    Else
    MsgBox "Duplicate"
    End If
    End Sub



    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.

    • Marked as answer by FoosMaster Sunday, December 25, 2016 5:59 AM
    Thursday, December 22, 2016 6:47 AM
    Moderator

All replies

  • Hi,

    We could use CountIf function to count the number of the range("B7") in the column J.

    Dim rng As Range
    Set rng = Range("J:J")
    rsl = WorksheetFunction.CountIf(rng, Range("B7"))
    If rsl = 0 Then
    Debug.Print "NOT Found"
    'Then paste
    End If

    Regards,

    Celeste


    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.

    • Marked as answer by FoosMaster Sunday, December 25, 2016 5:58 AM
    • Unmarked as answer by FoosMaster Sunday, December 25, 2016 5:59 AM
    Tuesday, December 20, 2016 6:50 AM
    Moderator
  • Hi Celeste,

     I just can't figure out how to get that to work. Did I mention I am a Beginner at this with Nobody to teach this old man anything?

    Maybe a better explanation...

    I have a button on each row of column 'A' from 'A2' thru 'A260'. There are names in column 'B' with info for each person in columns 'C' and 'D' on the same row as each name and the name with info needs to be sent to a different set of columns. Note:(There is usually, but not always, info for each person in columns 'C' and 'D')

    This is the current formula I am using:

    Sub a007AddToNextCell()

        ActiveSheet.Unprotect "password"

        Range("B7:D7").Select

        Selection.Copy

        x = Cells(Rows.Count, "J").End(xlUp).Row

        nar = Range("J1:J260" & x).Find("").Row

        Cells(nar, "J").Select

        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

            :=False, Transpose:=False

        Range("G7").Select

        Selection.ClearContents

        Range("B7").Select

        ActiveSheet.Protect "password"

    End Sub

    Example of what I need the formula to do:

    If I click the button in 'A7' then the name in 'B7' And the info in 'C7' and 'D7' needs to be posted to the first empty cell in column 'J' and same row of columns 'K' and 'L'. If the first empty cell in column 'J' is 'J4' then the name in 'B7' needs to be sent to cell 'J4' And the info in 'C7' needs to be sent to 'K4' and the info in 'D7' needs to be sent to 'L4' even if there is no information in 'K3' or 'L3'.

    I also tried to use this formula:

    Sub a007AddToNextCell()

        x = Cells(Rows.Count, "J").End(xlUp).Row

        nar = Range("J1:J260" & x).Find("").Row

        Cells(nar, "J").Value = Range("B7").Value

        x = Cells(Rows.Count, "K").End(xlUp).Row

        nar = Range("K1:K260" & x).Find("").Row

        Cells(nar, "K").Value = Range("C7").Value

        x = Cells(Rows.Count, "L").End(xlUp).Row

        nar = Range("L1:L260" & x).Find("").Row

        Cells(nar, "L").Value = Range("D7").Value

    End Sub

    That formula works the way I need by just changing the Value of the cells instead of doing a “Copy>Paste” But that formula posts the info to the first empty cell of Each column instead of the same row as the info in the first column “J” and I can’t figure out how to get it to work the way I need it to. 

    I also tried to modify the above formula to:

    Sub a007AddToNextCell()

        x = Cells(Rows.Count, "J").End(xlUp).Row

        nar = Range("J1:J260" & x).Find("").Row

        Cells(nar, "J").Value = Range("B7:D7").Value

    End Sub

    But that does not post the column “C” and “D” info. to columns “K” and “L”. 

    I use the top formula because I can't get the other 2 formulas to work properly.

    I would prefer to use a formula similar to the last formula to just change the Value of the cells instead of doing a “Copy>Paste” which requires the addition of code to the “Copy>Paste” formula to Unprotect the sheet at the beginning of the formula and then Protect the sheet at the end if I want the sheet to be protected.

    The Top formula I am using does post the info properly by doing a “Copy>Paste” instead of just changing the values but it doesn't check for a duplicate and will continue to post duplicates. I need the formula to Not post the info if the name being posted to column 'J' is already in column 'J' then, if possible, I need a pop-up message "Duplicate" to notify user about the attempted duplicate post.

    I would prefer to use a formula similar to the last formula on this post but my other formula that uses “Copy>Paste” would be OK if I could get it to Not paste if the info is a Duplicate.

    Thanks,

    Master Foo



    FoosMaster

    Tuesday, December 20, 2016 6:40 PM
  • Hi,

    If you don’t want to use Copy/Paste, you could use formula to reference the cells("B7")

    Sub End_of_List007()
    Dim rng As Range
    Set rng = Range("J:J")
    rsl = WorksheetFunction.CountIf(rng, Range("B7"))
    If rsl = 0 Then
    x = Cells(Rows.Count, "J").End(xlUp).Row
    nar = Range("J1:J260" & x).Find("").Row
    Cells(nar, "J").Formula = "=B7"
    Cells(nar, "J").Select
    Selection.AutoFill Destination:=Range(Cells(nar, "J"), Cells(nar, "L")), Type:=xlFillDefault
    Else
    MsgBox "Duplicate"
    End If
    End Sub



    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.

    • Marked as answer by FoosMaster Sunday, December 25, 2016 5:59 AM
    Thursday, December 22, 2016 6:47 AM
    Moderator