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
7,923
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
7,923
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,078,466
Messages
5,340,484
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top