VBA code to find a cell matching two criteria

NDMDRB

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

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


ABCD
1NumBarCodeQtyName
211011ClientA
321021ClientA
431031ClientA
541011ClientB
651011ClientC
761031ClientC

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,709
Office Version
2007
Platform
Windows
Try this

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

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

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

Code:
  Set r = ws.Range("D:D")
  Set f = r.Find(txt_Name, , xlValues, xlWhole)
and for each name it finds it compares the codbar

Code:
If ws.Range("B" & f.Row).Value = wBarC Then
If you find the codbar add the amount.

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

Watch MrExcel Video

Forum statistics

Threads
1,099,064
Messages
5,466,375
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top