Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Check Value if exist in a range and then....
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    • 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



    Any suggestions?

  2. #2
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    275
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    Hi,

    I'd use FIND:

    Note I used yourBarCode variable so you can provide Barcode you input somewhere
    Code:
    with 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) = .Range("U" & c.row + me.txtItemQty
                          else
                                  your other code
                          end if
                  end if
           end with
    end with
    Not I haven't tested it as I don't have possibility to do that now but it should work

    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


  3. #3
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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!!

  4. #4
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    275
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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.

    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


  5. #5
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    275
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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

    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


  6. #6
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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
    Last edited by NDMDRB; Oct 2nd, 2019 at 03:11 PM.

  7. #7
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    275
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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

    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.

    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


  8. #8
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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

  9. #9
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    275
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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


    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


  10. #10
    Board Regular
    Join Date
    Jun 2016
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check Value if exist in a range and then....

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •