VBA code to find a cell matching two criteria

[NDMDRB]

Hi,

I have a code that works fine with all conditions accept the one and need your help please

My code says:

• Check if the name is exist or not
  • If not, then GoTo new_item (it works great)
  • If exist and the BarCode is exist for the same name then just add the quantity (This is what I need)
  • If exist and the BarCode is not exist for the same name then GoTo new_item (it works great)

With my code below, I have 2 parts work fine unless second part
ex: If I need to add new data (BarCode= 103, Qty= 1, Name = ClientA)
Then, Cell "C4" should be 2

But I don't have the code to find Cell "C4" and add the quantity

Please help me getting this done and many thanks in advanced

Set c = ws.Range("D:D").Find(what:=txt_Name, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If c Is Nothing Then
        GoTo new_item
ElseIf Not c Is Nothing And Application.WorksheetFunction.CountIfs(ws.Range("B:B"), Me.BarCode, ws.Range("D:D"), Me.txt_Name) = 1 Then
        ws.Range("C" & c.Row) = ws.Range("C" & c.Row) + Me.txtQty
ElseIf Not c Is Nothing And Application.WorksheetFunction.CountIfs(ws.Range("B:B"), Me.BarCode, ws.Range("D:D"), Me.txt_Name) = 0 Then
        GoTo new_item

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"]A[/TD]
[TD="class: xl64, width: 64"]B[/TD]
[TD="class: xl64, width: 64"]C[/TD]
[TD="class: xl64, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Num[/TD]
[TD="class: xl64"]BarCode[/TD]
[TD="class: xl64"]Qty[/TD]
[TD="class: xl64"]Name[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]101[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientA[/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]102[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientA[/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]103[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientA[/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]101[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientB[/TD]
[/TR]
[TR]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]101[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientC[/TD]
[/TR]
[TR]
[TD="class: xl64"]7[/TD]
[TD="class: xl64"]6[/TD]
[TD="class: xl64"]103[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]ClientC[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  Dim r As Range, f As Range, ws As Worksheet, cell As String, wBarC As Variant
  Set ws = ActiveSheet
  Set r = ws.Range("D:D")
  Set f = r.Find(txt_Name, , xlValues, xlWhole)
  If IsNumeric(BarCode.Value) Then wBarC = Val(BarCode.Value) Else wBarC = BarCode.Value
  If Not f Is Nothing Then
      cell = f.Address
      Do
          If ws.Range("B" & f.Row).Value = wBarC Then
             ws.Range("C" & f.Row) = ws.Range("C" & f.Row) + txtQty
            Exit Do
          End If
          Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

 

[NDMDRB]

Thank you so much DanteAmor for your help,

Your suggestion is great and I used it in another situation, but it didn't work as I need with this code, I thing that I didn't explain well or I missed important details

Anyway, if you may help me just find the serial number ("A:A") of the row that has The same Name and the same BarCode (in my example above I need to find number 3 [Cell "A4"])
And Then I will use the find function and add the quantity

I tried Index and Match function as below but it didn't work well

Set b = Application.WorksheetFunction.Index("A:A", Application.WorksheetFunction.Match(BarCode, "C:C", 0), Application.WorksheetFunction.Match(c, "I:I", 0))

 

[DanteAmor]

Did you try the code for what you asked for?


For that it is that code, to realize a search several times and to be comparing one or several criteria.


I explain to you:
The code searches column D for the name

  Set r = ws.Range("D:D")
  Set f = r.Find(txt_Name, , xlValues, xlWhole)

and for each name it finds it compares the codbar

If ws.Range("B" & f.Row).Value = wBarC Then

If you find the codbar add the amount.

ws.Range("C" & f.Row) = ws.Range("C" & f.Row) + txtQty

----------------------------------------------------------------------------

For the next thing you ask, I don't understand.
You are not using the Index function correctly.
In your example you have the name in column D and in your code you put column I.


I will gladly help you with the code, but you must explain again what you want. Forget your code for a moment.
Put the examples again and I help you with all the code.