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?
 
Sort by date Sort by votes
Wait. I will make this code cooler ;)
 
Last edited:
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Ok. I had to make another post as 10 mins passed after my last post :(
Oh, well. It happens ;)

Try this one:

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
        Else
            'goto "new_item tag which holds big piece of macro shared by another condition: if no barcode is found
            GoTo new_item
        End If
    Else
        'goto "new_item" tag which holds big piece of macro shared by another condition: barcode is found but column G equals 0
        GoTo new_item
    End If
    
    GoTo finish


'I put this piece of code here as you want to use it with two cases. There is no need to repeat it. You can do like this:
new_item:
    
        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") = ""
        
        GoTo finish
        
End With


finish:


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




ListBox1.ListIndex = -1
Me.txtBarCode.SetFocus




End Sub
 
Upvote 0
Hello nardagus how are you today,


I really appreciate your time and your effort to make this happen, and really so sorry to bothering you again, there is still one thing, but I'm so shy to ask you again,I tried so hard to fix it but unfortunately didn't work, but if you have no time it's really okay and still appreciate your help.


What's happening now is:


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 (GoTo new_item)
4) If barcode is not found (GoTo new_item)

This works great

But in case these steps happened for one BarCode (let's Say "2")
*1): If barcode is not found (GoTo new_item) (This works correctly)
*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 (This works correctly)
*3): If I gave this item as a gift, so the amount will be 0
*4): If barcode is found, but a cell in column G is 0 then (GoTo new_item) (For now every thing works correctly)
*5): In case I'll add the same BarCode again, since this Barcode is found in two rows (1, with amount "0" & 1, with normal amount) it should found the normal amount and add "1" to the quantity, but with my code it add another item in another row


Briefly: If barcode is found, but a cell in column G is 0 then (GoTo new_item)
(GoTo new_item) will happen every time I add this barcode, even if it's found with normal amount


But I just need to add one line for each barcode, or, if I make it as a gift then add another line, and then just add the quantity
 
Upvote 0
Hi,

Try now:

Code: 
Private Sub txtBarCode_Change()

Dim ws As Worksheet
Dim frng As Range
Dim fcell As Range


Set ws = Sheet2


'set finding range for not to repeat whole range text
Set frng = ws.Range("C3:C9999")


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
    
    Set c = frng.Find(What:=Me.txtBarCode)
    
    If Not c Is Nothing Then
        Set fcell = c
        Do
            If .Range("G" & c.Row) > 0 Then
                .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtItemQty
            End If
            
            Set c = frng.FindNext(c)
            'stop searching when no value is found
            If c Is Nothing Then
                Exit Do
            End If
        'exit find loop if find jumped to the top again
        Loop While fcell.Address <> c.Address
    Else
        'goto "new_item" tag which holds big piece of macro shared by another condition: barcode is found but column G equals 0
        GoTo new_item
    End If
    
    GoTo finish




'I put this piece of code here as you want to use it with two cases. There is no need to repeat it. You can do like this:
new_item:
    
        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") = ""
        
        GoTo finish
        
End With




finish:


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


ListBox1.ListIndex = -1
Me.txtBarCode.SetFocus


End Sub
 
Upvote 0
Thank you again nardagus and so sorry to get back with problems

First, the given code didn't work if there is any gift (I mean ex: if the amount for Item "3" is "0" then the code do nothing) but in case the amount is >0 then it works great
Second, I removed "On Error Resume Next" then I got an error with the line ".Cells(nr, "C") = CDbl(Me.txtBarCode)" and still can't fix the problem
 
Upvote 0
Hello nardagus, how are you today

Did you find any solution for my problem?
Many thanks for your time and so sorry for bothering you
 
Upvote 0
Hi, Sorry for late response.

You aren't bothering me. It is me who should say SORRY for it takes too long... :(

I hope this time the code contains all cases you needed. If not I swear I remove my account as clearly I'm not up to the helping task anymore... ;)
(It's not that I'm here every day... I lack of time...)
Anyway.

This time my code does:
1) If Barcode is not found then GoTo new_item
2) If Barcode is found then
a) if column G is greater then 0, then it runs this -> .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtItemQty
b) if column G is equal to 0 and given barcode exists only once in Sheet2 then GoTo new_item
c) if barcode exists twice it runs this -> .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtItemQty, but only for row where G > 0


Code: 
Private Sub txtBarCode_Change()

Dim ws As Worksheet
Dim frng As Range
Dim fcell As Range




Set ws = Sheet2




'set finding range for not to repeat whole range text
Set frng = ws.Range("C3:C9999")




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
    
    Set c = frng.Find(What:=Me.txtBarCode)
    
    If Not c Is Nothing Then
        Set fcell = c
        Do
            If .Range("G" & c.Row) > 0 Then
                .Range("F" & c.Row) = .Range("F" & c.Row) + Me.txtItemQty
            ElseIf .Range("G" & c.Row) = 0 And Application.WorksheetFunction.CountIf(.Range("C:C"), Me.txtBarCode) <= 1 Then
                GoTo new_item
            End If
            
            Set c = frng.FindNext(c)
            'stop searching when no value is found
            If c Is Nothing Then
                Exit Do
            End If
        'exit find loop if find jumped to the top again
        Loop While fcell.Address <> c.Address
    Else
        'goto "new_item" tag which holds big piece of macro shared by another condition: barcode is found but column G equals 0
        GoTo new_item
    End If
    
    GoTo finish








'I put this piece of code here as you want to use it with two cases. There is no need to repeat it. You can do like this:
new_item:
    
        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") = ""
        
        GoTo finish
        
End With








finish:




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




ListBox1.ListIndex = -1
Me.txtBarCode.SetFocus




End Sub
 
Last edited:
Upvote 0
Hi, how are you today nardagus,

First of all I don't know how to thank you enough for your help and hard work,
I really appreciate every single moment you spend fixing my code until it works really amazing

At the end, I will say that you can keep going with your amazing account and keep helping others ;) hehe

Thank you so mach dear
 
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
407
brianv
B
Y
  • Question Question
Help me find error in this coding
Replies
6
Views
537
Peter_SSs
Peter_SSs
Y
  • Question Question
Sum the total working hours when the Qty Completed become value except 0 automatically
Replies
0
Views
427
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
202
ipbr21054
I

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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