How can I search a column for duplicate then transfer data from UserForm to next empy row?

[gmr4evr1]

Howdy everyone,
I have a workbook (Attached) with a UserForm (very basic for the purpose of this question).
What I would like to do is when the 'Submit" button is pressed, the value for the ComboBox (named Press) is checked against Sheet1 Column B for duplicates. If a duplicate IS found, then transfer the value from Press (ComboBox) to the next available row in Column B AFTER the duplicate value.
If a duplicate is NOT found, then find the last used row, skip2 rows then place the value from Press (ComboBox) in the 3rd row of Column B.

Search B for same then next blank row.xlsm

[mehmetcik]

Please Login or Register  to view this content.

[gmr4evr1]

mehmetcik,
PERFECT!! I had a feeling you might be the one to help me out with this, thank you very much. I noticed that I forgot to mention that when it does find a duplicate, I need the data from Press(ComboBox) to go into the next row after the duplicate value, which you have done.....But, I also need it to insert another row after that data is transferred. I think I might be able to figure this part out though.

Thanks a bunch!
P.S.>
The code I was trying to come up with on my own was wayyyyyy more complicated than yours is.

[gmr4evr1]

mehmetcik,
Well, I thought we had it there, but after going through it a bit I realize that it isn't quite working the way I wanted, and I haven't been able to figure it out. I had something that I thought was going to work, but, it didn't. I have attached the workbook with your code in the UserForm, and my code in Sheet1 VBA properties. On Sheet1 you will see the result I am getting and the desired result.

If you have questions, refer to the 1st post in this thread or feel free to ask me.
TIA
Dang it...I forgot the attachment!!!!!! Here it is
456169d1460661317-how-can-i-search-a-column-for-duplicate-then-transfer-data-from-userform-to-n.xlsm

[jindon]

Is this how you wanted?

Please Login or Register  to view this content.

[gmr4evr1]

jindon,
Thank you for helping out here. I tried your code but it didn't quite do what I'm trying to accomplish. I have attached the file so you can see what it's doing, and what I'm trying to have it do.
456169d1460661317-how-can-i-search-a-column-for-duplicate-then-transfer-data-from-userform-to-n.xlsm

[jindon]

Ahh, try change to

Please Login or Register  to view this content.

[gmr4evr1]

By George I think you've done it!!!
I did a quick test on it and it seems to be doing exactly what I needed.
Thanks a bunch jindon!!!

[jindon]

You are welcome and thanks for the rep.

Please don't forget to mark the thread as "Solved".

[gmr4evr1]

Oops...done

[gmr4evr1]

jindon,

I know I have marked this as solved, but I found an issue. I had forgotten that there are formulas involved in the sheet I am working with and was wondering if there is a way to -
1) Add the formulas from Column H from the cell above to the newly created row(s) with the correct row referenced in the formula
2) Add the newly created row(s) into the formula to the cell below

Attached is a sample workbook with a (hopefully) better explanation.
Copy of 20160111 Utilization - Elster Impact with UserForm.xlsm
If I need to create a new post for this, let me know.

Also, here is the code that you provided with my modifications..

Please Login or Register  to view this content.

[jindon]

See if this works.

Please Login or Register  to view this content.

[gmr4evr1]

jindon,
You are AMAZING!!!! Thank you very much for the new code, it works flippin beautifully!! I'm almost POSITIVE that I have checked and double checked everything to make sure that this is all I needed help with.
Thanks AGAIN!