VBA Code to insert the same data for many clients!!

[NDMDRB]

Hi,

I have a userform with many controls, and below are some of them

• txtBarCode (TextBox) [where I insert the needed Item's Barcode]
• cmbNum (ComboBox) [(List from 1 to 8) to Identify the clients number]
• txt_Name1, txt_Name2 ...txt_Name8 (TextBox) [To insert the client names, (if cmbNum = 3 then I should insert 3 names...)]
• cmdAdd (CommandButton) [to add the inserted values to sheet16]

The above are the main controls that I need in this inquiry

ex: If the BarCode is "101" and I need to add this item to 4 clients, then

• txtBarCode = 101
• cmbNum = 4
• txt_Name1 = Client A
• txt_Name2 = Client B
• txt_Name3 = Client C
• txt_Name4 = Client D

I have this piece of code in "cmdAdd" to insert the above data to sheet16

Dim fcell As Range


With ws
        For i = 1 To cmbNum.Value
        Set c = .Range("I:I").Find(What:=Me.Controls("txt_Name" & i), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)


    If Not c Is Nothing Then
        Set fcell = c


        Do
            If .Range("C" & c.Row) = BarCode And .Range("I" & c.Row) = Me.Controls("txt_Name" & i) Then
                .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtQty
            End If


            Set c = .Range("I:I").FindNext(c)


        'exit find loop if find jumped to the top again
        Loop While fcell.Address <> c.Address
    Else


        GoTo new_item
    End If
Next i
    GoTo finish

What I need is:

• Check if the name is exist and has the same BarCode, then just add 1 to the quantity
• Else, GoTo new_item
• Then check the second name...

With the above code, if the all the clients names don't exist in sheet16, then the code will run as I need
Else if all the names are exist with BarCode "101" or any other BarCode, then the code will run as I need

But ex: if I need to insert BarCode "101" and these names: (Client A, Client B, Client E, Client F) then

• The quantity for Client A & Client B will be +1 and the same data for these 2 names will be added as new row(while I just need to add the quantity)
• Client E & Client F will be added as new row (as I need)

Please help me fixing my code
I'm not sure if I explained what I need well, but if you need any more details please let me know

Many thanks in advanced

 

[DanteAmor]

I did not understand what the problem is.
Maybe you can explain it using data from your sheet.

Forget the code a bit and explain what you need.

 

[NDMDRB]

Let's say that Sheet16 contains these data

[TABLE="width: 902"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Num[/TD]
[TD]Inv[/TD]
[TD]BarCode[/TD]
[TD]Item Name[/TD]
[TD]Unit Price[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Category[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]101[/TD]
[TD]Test1[/TD]
[TD]10$[/TD]
[TD]1[/TD]
[TD]10$[/TD]
[TD]Category1[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]102[/TD]
[TD]Test2[/TD]
[TD]15$[/TD]
[TD]1[/TD]
[TD]15$[/TD]
[TD]Category1[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]103[/TD]
[TD]Test3[/TD]
[TD]20$[/TD]
[TD]1[/TD]
[TD]20$[/TD]
[TD]Category1[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]101[/TD]
[TD]Test1[/TD]
[TD]10$[/TD]
[TD]1[/TD]
[TD]10$[/TD]
[TD]Category1[/TD]
[TD]Client B[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]101[/TD]
[TD]Test1[/TD]
[TD]10$[/TD]
[TD]1[/TD]
[TD]10$[/TD]
[TD]Category1[/TD]
[TD]Client C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]…[/TD]
[TD]…[/TD]
[TD]102[/TD]
[TD]Test2[/TD]
[TD]15$[/TD]
[TD]1[/TD]
[TD]15$[/TD]
[TD]Category1[/TD]
[TD]Client C[/TD]
[/TR]
</tbody>[/TABLE]


Now, if I need to add:

• txtBarCode = 101 (That Already exist)
• cmbNum = 4
• txt_Name1 = Client A (That already exist with barcode 101)
• txt_Name2 = Client B (That already exist with barcode 101)
• txt_Name3 = Client C (That already exist with barcode 101)
• txt_Name4 = Client D (New Client)

What I need is to check two conditions, (BarCode & Client Name)

• If both conditions match, then just add the quantity and then check the second name
• If one of these conditions doesn't match, then ad new line with the inserted data (In this case (F2, F5 & F6 should be 2) and Client D should be in new row (Row8)

I hope I explained the problem well, many thanks in advanced

 

[DanteAmor]

Try this.
- Change Sheet16 for the name of your sheet
- Combo is not necessary, you must go through the 8 textbox
- If txtBarCode is number then use the Val function otherwise only the textbox
- If any of the 2 values ​​is not found, the record is added, type the remaining fields.

Private Sub CommandButton1_Click()
  Dim f As Range, r As Range, ws As Worksheet, cell As String
  Dim lr As Long, exists As Boolean
  Set ws = Sheets("[COLOR=#ff0000]Sheet16[/COLOR]")
  Set r = ws.Range("I:I")
[COLOR=#0000ff]  For i = 1 To 8[/COLOR]
    exists = False
    If Controls("txt_Name" & i) <> "" Then
    Set f = r.Find(Controls("txt_Name" & i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        If ws.Range("C" & f.Row) = [COLOR=#ff0000]Val[/COLOR](txtBarCode.Value) Then
[COLOR=#008000]          'If both conditions match, then just add the quantity[/COLOR]
          ws.Range("F" & f.Row) = ws.Range("F" & f.Row) + txtQty
          exists = True
          Exit Do
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
    End If
    If exists = False Then
[COLOR=#008000]      'If one of these conditions doesn't match[/COLOR]
[COLOR=#0000ff]      lr = ws.Range("I" & Rows.Count).End(xlUp).Row + 1[/COLOR]
[COLOR=#0000ff]      ws.Range("C" & lr) = txtBarCode.Value[/COLOR]
[COLOR=#0000ff]      ws.Range("F" & lr) = txtQty.Value[/COLOR]
[COLOR=#0000ff]      ws.Range("I" & lr) = Controls("txt_Name" & i).Value[/COLOR]
    End If
    End If
  Next
End Sub