Insert a unique value in a column

  • I need to insert a unique value in a colum


    I am getting the value from the user and then inserting it in the next available empty cell in the column


    But Before inserting I need to check that this value is previously not present in the column.


    please provide a sol.

  • It would help if we knew what can of information - numerical or text for instance.


    To find the next available cell use :


    Dim InPut


    InPut= InputBox("Enter data")


    Range("A65536").End(xlUp).Offset(1,0).Value=Input


    This will enter the users input into the next empty cell in Column A

  • Hi Roy


    I need to check the value while inserting in the column


    Most of the discussions in the forums are linked to getting the unique values in the previously filled columns


    I want to implement the following logic


    get the input from the user via input box
    check for this value in the range and if it is not present in the colum insert this value in the next available cell in the range

  • Try this




  • Hi Roy thanks for this sol.


    Can you help in this prob. also


    This is regarding stopping and resuming of a proc


    I want to do the following :


    I have 4 buttons in my worksheet


    button 1 (Operation 1) :


    by clicking on this I am doing certain set of operations (operation1) repeatedly.
    let say its a public sub procedure which I will be looping
    This a procedure which is looped through application.ontime.


    button 2 (Suspend) :


    for suspending the operation1
    current I am using stop command for this functionality. I do not want to use wait as I want to do some other set of operations and do not want to use end as I want to resume from the point where I have stopped.


    button 3 (operation 2)


    And after this i will be able to click a 3rd button (Operation2) to do some other set of operations ( operation 2 )
    operation 2 is a small set of statements executed only once and no looping.



    button 4 (Resume Operation 1)


    after my operation 2 finishes I should be able to click on this button and resume my operation 1 from the point where I have stopped it


    The additional thing here I require is I will pause a macro or a proc at a certain line ( whenever stop button is pressed ) and then will execute some other proc ( get a notification when it will finish ) and after that will resume the operation1 from the same point where it was stopped .


    The code will be similarly be something like this


    'this is button1 to start operation 1


    sub commandbutton1_click()


    operation1


    end sub


    'this code is for operation 1


    sub operation1()


    'some statements to execute
    --------
    --------------
    ------------


    application.ontime now + timevalue("00:00:30"), "Call_again"


    end sub


    'this is the repeating procedure


    sub call_again()


    operation1


    end sub


    ' The code for the Stop button which will execute the stop command and will pause the program execution of operation 1


    sub commandbutton2_click()
    stop
    end sub


    now at this point I want to call operation2 through my third button


    sub CommanButton3_click ()
    operation2
    end sub



    sub operation2()
    some set of statements
    -------------------
    -------------------
    -------------------
    msgbox "operation2 finishes"
    end sub


    after this I want Resume the Operation 1 from the point I have stopped by pressing the button 4 resume .


    sub commandbutton4_click()


    logic for resuming the operation1


    end sub


    please provide some solution .


    Sachin