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

N

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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 :)
 
Upvote 0
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!!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.

[TABLE="class: grid, width: 816"]
<tbody>[TR]
[TD]Inv. #[/TD]
[TD]Num[/TD]
[TD]BarCode[/TD]
[TD]Item Name[/TD]
[TD]Unit Price[/TD]
[TD]Qty[/TD]
[TD]Amount[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Test1[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD]Test1[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD]Test2[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD]Test2[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD]Test3[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD]Test3[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3000[/TD]
[TD]Games[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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. :)
 
Upvote 0
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
 
Upvote 0
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

 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
400
brianv
B
Y
  • Question Question
Help me find error in this coding
Replies
6
Views
536
Peter_SSs
Peter_SSs
Y
  • Question Question
Sum the total working hours when the Qty Completed become value except 0 automatically
Replies
0
Views
425
yoon
Y
L
  • Question Question
VBA to export recurring meeting data
Replies
1
Views
1K
John_w
J
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top