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

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
128
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

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,857
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jun 20, 2016
Messages
128
Let's say that Sheet16 contains these data

ABCDEFGHI
1NumInvBarCodeItem NameUnit PriceQtyAmountCategoryClient Name
2101Test110$110$Category1Client A
3102Test215$115$Category1Client A
4103Test320$120$Category1Client A
5101Test110$110$Category1Client B
6101Test110$110$Category1Client C
7102Test215$115$Category1Client C

<colgroup><col><col span="2"><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>


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

Well-known Member
Joined
Dec 3, 2018
Messages
8,857
Office Version
2007
Platform
Windows
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.

Code:
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
 
Last edited:

Forum statistics

Threads
1,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top