VBA to find identical value in column and insert new line with data

[WSD]

Using Excel 2010

Hello,

I am new to VBA so please bear with me =).
I have a userform which requires approx. 10 fields to be entered by the user, which once submitted populates each cell in a row with the entered data (each field has a separate cell).

I need the following to happen when entering the data into the userform:
If the value of field 3 matches the value of any cell in column A, prompt user to "OK" or "Cancel".
If "OK", then goto the row with the matching value and insert the new data from fields 1 and 2 into a new, subsequent row.

Sorry if this sounds vague, I'll try to answer any questions that may be of use.
Thank you for any assistance!

 

[JLGWhiz]

Using Excel 2010

Hello,

I am new to VBA so please bear with me =).
I have a userform which requires approx. 10 fields to be entered by the user, which once submitted populates each cell in a row with the entered data (each field has a separate cell).

I need the following to happen when entering the data into the userform:
If the value of field 3 matches the value of any cell in column A, prompt user to "OK" or "Cancel".
If "OK", then goto the row with the matching value and insert the new data from fields 1 and 2 into a new, subsequent row.

Sorry if this sounds vague, I'll try to answer any questions that may be of use.
Thank you for any assistance!

The part that I don't understand is what a Field is on a UserForm. Is it a Label, A TextBox, a selection from a ListBox or ComboBox, and if the latter is the is it multiselect, with or without bound column, etc. It helps to use terminology that is in the Excel/VBA conventions.

 

[WSD]

Thanks for the response, I appreciate your time.

I'll give an example using vba terminology.

Eg.
Userform has the following (letters also correspond to the columns which the data is fed to):

Enter name: textboxA
Enter item: textboxB
Enter price: textboxC
Choose colour: comboboxD

If textboxC's exact value is already located in any row in columnC, insert row below that duplicate value and add textboxA.value to the new row's columnA, add textboxB.value to columnB, and add textboxC.value to columnC (comboboxD can remain blank).

I hope that helps,
Thanks again!

 

[JLGWhiz]

The userform must be visible while this code runs. The code is to be placed in the UserForm code module. Open the VBA editor, right click on the UserForm name in the projects pane at upper left of the screen. click on view code in the pop up menu. When the code window opens copy the code below and paste into the window. The code needs some editing. I am not sure what your sheet name is, I used sheet index 1, but that needs to be edited. Also, I am not sure if the names you listed for the text boxes are codenames or what, so you need to edit those and use a name that VBA will recognize for each text box. You can do that by selecting them while in design mode and looking in the properties list to see what name appears there. But this is the basic code that would otherwise do the job. I also made the click event of the UserForm the trigger to run the code. So after the text boxes are loaded, just click the UserForm to run the code. You can change that to suit your own purposes, but I needed somewhere to start.

Code:
Private Sub UserForm_Click()
Dim sh As Worksheet, lr As Long, rng As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = sh.Range("C2:C" & lr)
Set c = rng.Find(Me.TextBoxA.Value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(1).Row.Insert
With sh
.Range("A" & c.Row + 1) = Me.TextBoxA.Value
.Range("B" & c.Row + 1) = Me.TextBoxB.Value
.Range("C" & c.Row + 1) = Me.TextBoxC.Value
End With
End If
End Sub
Code: