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

NDMDRB

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

Active Member
Hi,

I'd use FIND:

Note I used yourBarCode variable so you can provide Barcode you input somewhere
Code:
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

Board Regular
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

Active Member
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

Active Member
Hello,

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

I fixed a bug. Try now:

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

nardagus

Active Member
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. #NumBarCodeItem NameUnit PriceQtyAmountCategory
10011
10021Test1100061000Games
1003
10041Test1100011000Games
10052
1006
10072Test2200012000Games
1008
10092Test2200012000Games
100103
101113Test3300013000Games
102123Test3300013000Games
41011

<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

Board Regular
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

Active Member
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

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

Board Regular
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 e
xcept 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)
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top