Check Value if exist in a range and then....

[NDMDRB]

Hello,

I have the following code to add the input BarCode and it's related details to the next available raw in sheet2 column ("P")

Here is a piece of my code that works fine:

Dim ws As WorksheetSet ws = Sheet2


nr = ws.Cells(Rows.Count, "P").End(xlUp).Row + 1

ws.Cells(nr, "P") = CDbl(Sheet3.Cells(Rows.Count, 1).End(xlUp).Value + 1)
ws.Cells(nr, "Q") = Sheet2.Cells(Rows.Count, "Q").End(xlUp).Value + 1
ws.Cells(nr, "R") = CDbl(Me.txtBarCode)
ws.Cells(nr, "S") = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 2, 0)
ws.Cells(nr, "T") = CDbl(Format(Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 4, 0))
ws.Cells(nr, "U") = CDbl(IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0)
ws.Cells(nr, "V") = CDbl(Me.txtPrice * (IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0))


What I need once I enter any BarCode is:

[LIST]
[*]Check if the entered Barcode is exist or not in ws.Range("R5:R9999")
[*]if exist, and the same raw, column "V" has a value greater than "0" then
[*]Same raw, Column "U".value = (Same raw, Column "U".value) + me.txtItemQty
[*]Else, do the above code
[/LIST]


Any suggestions?

 

[nardagus]

Hi,

I'd use FIND:

Note I used yourBarCode variable so you can provide Barcode you input somewhere

with [COLOR=#333333]ws
       with .Range("R5:R9999")
              set c = .Find(What:=yourBarCode)
              
              if not c is nothing then
                      if .Range("V" & c.row) > 0 then
                              .Range("U" & c.row) = [/COLOR][COLOR=#333333].Range("U" & c.row + [/COLOR][COLOR=#333333]me.txtItemQty
[/COLOR][COLOR=#333333]                      else
                              your other code
                      end if
              end if
       end with
[/COLOR]end with

Not I haven't tested it as I don't have possibility to do that now but it should work

 

[NDMDRB]

Thank you so much nardagus,

I put the same code you provided, just changed {set c = .Find(What:=yourBarCode)} to {set c = .Find(What:=me.txtBarCode)}

It didn't get any error, but it didn't add any of the input data, Sheet2.Range("P:V") keeps empty

I don't know what is missing here!!

 

[nardagus]

Hello,

Sorry for late response.
First question. Did you suppress error handling?
If yes then for macro developing you shouldn't do that. Error suppressing makes debugging harder.

As for rest. Hmmm. My piece of code works for me. I need to check it more thoroughly. Will do that later in the evening.

 

[nardagus]

Hello,

I checked your code and it really seems you suppressed error handling

I fixed a bug. Try now:

Dim ws As Worksheet
Set ws = Arkusz2


With ws
    nr = .Cells(Rows.Count, "P").End(xlUp).Row + 1
    
    With .Range("R5:R9999")
        Set c = .Find(What:=Me.txtBarCode)
    End With
    If Not c Is Nothing Then
        If .Range("V" & c.Row) > 0 Then
            .Range("U" & c.Row) = .Range("U" & c.Row) + Me.txtItemQty
        Else
            .Cells(nr, "P") = CDbl(Sheet3.Cells(Rows.Count, 1).End(xlUp).Value + 1)
            .Cells(nr, "Q") = Sheet2.Cells(Rows.Count, "Q").End(xlUp).Value + 1
            .Cells(nr, "R") = CDbl(Me.txtBarCode)
            .Cells(nr, "S") = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Arkusz1.Range("A4").CurrentRegion, 2, 0)
            .Cells(nr, "T") = CDbl(Format(Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 4, 0)))
            .Cells(nr, "U") = CDbl(IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0)
            .Cells(nr, "V") = CDbl(Me.txtPrice * (IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0))
        End If
    End If
End With

 

[NDMDRB]

Thank you so much nardagus for you time, but sorry to inform you that it's still the same problem, it didn't get any values

Please find my test file link below and please help me finding what I missed

Many thanks in advanced

https://drive.google.com/file/d/1rT6Kh4mKnNzgRGgJM-unBNL5_6S6Rg4X/view?usp=sharing

 

[nardagus]

Ok. I've checked your file.

First of all. If you used "change" event be carefull when changing txtBarcode value as excel will detect it and will reset your macro, so rest of the code which is after this: txtBarCode = "" won't work.
Despite the above your code seems to be working.
I'm not sure what is your goal in this exercise, but when I put a value in Barcode column of Sheet2 I got some results.

Inv. #	Num	BarCode	Item Name	Unit Price	Qty	Amount	Category
100	1	1
100	2	1	Test1	1000	6	1000	Games
100	3
100	4	1	Test1	1000	1	1000	Games
100	5	2
100	6
100	7	2	Test2	2000	1	2000	Games
100	8
100	9	2	Test2	2000	1	2000	Games
100	10	3
101	11	3	Test3	3000	1	3000	Games
102	12	3	Test3	3000	1	3000	Games
		4			101	1

<tbody>
</tbody>

I assume that when Barcode is found and column G is greater then 0 all other columns are already filled?
And if column G is equal 0 then you add whole new row based of information from Sheet1? (at least that's how this code works for me)

Let me know what exactly do you want to achieve and then maybe we can find a solution.

 

[NDMDRB]

First of all I really appreciate you help,nardagus

Regarding the change event, I have this peace of code at the beginning that exit the sub in case txtBarCode is empty (If txtBarCode = "" Then Exit Sub) this just helps me to cross the null columns and works fine

Regarding you code, it is really works very well just in case there is at least one raw entered after raw 4 in Sheet2

But in case Raw 5 in Sheet2 is empty, it will not work, not even getting an error, and nothing will happen

 

[nardagus]

Hmm I think I know how you want to use this.

However let me check it:

1) excel should look for Barcode in sheet2
2) if found, it should check if a cell in column G is greater than 0. If yes you do this: .Range("U" & c.row) = .Range("U" & c.row + me.txtItemQty
3) If barcode is found, but a cell in column G is 0 then nothing happens
4) If barcode is not found you run rest of the macro.

Right? If I'm right try the code below.
I took a liberty to do some cleaning of the code. I hope it's ok.

Couple of tips, if I may:
- If you use WITH with a sheet or a range there is no need to repeat sheet name/variable or range later. At least until you close it with END WITH
- remember of code formatting. This helps reading the code later
- use comments. This helps understanding a code when you need to modify it later
- "On Error Resume Next" - don't use it until you really need that. That will make code debugging much harder.

Let me know if

Private Sub txtBarCode_Change()

Dim ws As Worksheet
Set ws = Sheet2


On Error Resume Next


If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtBarCode.Value) = 0 Then
    Me.lblAlert.Caption = "This Item does not exist!!!"
    Exit Sub
End If


With ws
    nr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    With .Range("C3:C9999")
        Set c = .Find(What:=Me.txtBarCode)
    End With
    
    If Not c Is Nothing Then
        If .Range("G" & c.Row) > 0 Then
            .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtItemQty
       End If
    Else
        nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(nr, "A") = CDbl(.Cells(Rows.Count, 1).End(xlUp).Value + 1)
        .Cells(nr, "B") = CDbl(.Cells(Rows.Count, 2).End(xlUp).Value + 1)
        .Cells(nr, "C") = CDbl(Me.txtBarCode)
        .Cells(nr, "D") = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 2, 0)
        .Cells(nr, "E") = CDbl(Format(Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 4, 0), "#,##0"))
        .Cells(nr, "F") = CDbl(IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0)
        .Cells(nr, "G") = CDbl(ws.Cells(nr, "E") * (IIf(Me.txtItemQty = "", 1, Me.txtItemQty) + 0))
        .Cells(nr, "H") = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 3, 0)
        
        txtBarCode = ""
        txtItemQty = "1"
        nr = ws.Cells(Rows.Count, "C").End(xlUp).Row + 1
        .Cells(nr, "A") = ""
        .Cells(nr, "B") = ""
        .Cells(nr, "F") = ""
        .Cells(nr, "G") = ""
    End If
End With




ListBox1.RowSource = Sheet2.Range("Sales_List").Address(external:=True)


ListBox1.ListIndex = -1
Me.txtBarCode.SetFocus


End Sub

 

[NDMDRB]

Amazing nardagus, this works even if sheet 2 is empty, but just missing one thing

1) excel should look for Barcode in sheet2
2) if found, it should check if a cell in column G is greater than 0. If yes you do this: .Range("U" & c.row) = .Range("U" & c.row + me.txtItemQty
3) If barcode is found, but a cell in column G is 0 then nothing happens
4) If barcode is not found you run rest of the macro.

This is exactly what I need except number (3)

3) should be: If barcode is found, but a cell in column G is 0 then run the rest of the macro (same as (4))


Here is why: Cell G sets "0" just in case I need to give any client a gift, so the amount will be "0",
But in case I'll add the same item but as normal item, I need to be in a new line with it's price, if again the same item, repeat number (2)