Simple implementation question

[Katolux]

Hi,

This is my first thread, so if there is anything I am doing wrong please feel free to let me know.

I have a really simple VBA code to be able to scan a code bar, check it with an inventory list and copy the information in a 3rd sheet to control sells.
I am trying to implement a simple code to check if the Barcode has already been sold and to give me back a message if so. If not, continue with the normal code, but I am not sure in which part of my code (Macro1) should i call Macro 2 for the implementation and check.


Code in Sheet Scan :

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
     
        Call receive
        Application.EnableEvents = True
    End If

End Sub

Code in Macro 1:

Sub receive()
Dim barcode As String
Dim rng As Range
Dim rown, lrow As Long
Dim qty As Long


barcode = Tabelle1.Cells(2, 3)
Tabelle2.Activate
'is there a barcode
If barcode = "" Then Exit Sub
If barcode <> "" Then

    Set rng = Tabelle2.Columns("A:A").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)

     
        If rng Is Nothing Then
            MsgBox "Barcode nicht gefunden"
            GoTo ende
        Else
        e
            rown = rng.Row
            
            If barcode = "" Then Exit Sub
            If barcode <> "" Then
            
                Tabelle2.Cells(rown, 8).Value = Tabelle2.Cells(rown, 8).Value
             
                Tabelle2.Range(Cells(rown, 2), Cells(rown, 7)).Copy
                Tabelle3.Activate
           
                lrow = Tabelle3.Cells(Rows.Count, 2).End(xlUp).Row + 1
                Tabelle3.Cells(lrow, 2).PasteSpecial
           
                Tabelle3.Cells(lrow, 1).Value = barcode
            
                Tabelle3.Cells(lrow, 8) = Date & "  " & Time
                Tabelle3.Cells(lrow, 8).NumberFormat = "m/d/yyyy h:mm"
                MsgBox "Registered"
                GoTo ende
            End If
           
         End If
    
End If
    
ende:
'turn off the marching ants
Application.CutCopyMode = False
Tabelle1.Activate

Tabelle1.Cells(2, 3).ClearContents

ActiveWorkbook.Sheets("scan").Activate
Sheets("scan").Range("C2").Select '(and activate)
End Sub

Code in Macro 2 :

Sub TestForDuplicates()
Dim rng As Range

'Store Range to search through
  Set rng = Range("A2:A3")

'Test Range for Duplicates
  If Evaluate(Replace("NOT(AND((COUNTIF(@,@)=1)))", "@", rng.Address)) = True Then
    MsgBox "Product already sold"

End Sub

At some point I will like to transform sheet 1 into a simple interface in which you click, use the barcode scanner and press enter, but I first need to have the basic code working.

I studied some VBA years ago but never got it to use until this moment, and I am really rusty. There is a big chnace that the code could be so more efficient, but this is the best I got at the moment.

Thank you for any help and advice you can provide me!

 

[Katolux]

I am realizing I explained myself badly here

I have a really simple VBA code to be able to scan a code bar, check it with an inventory list and copy the information in a 3rd sheet to control sells.
I am trying to implement a simple code to check if the Barcode has already been sold and to give me back a message if so. If not, continue with the normal code, but I am not sure in which part of my code (Macro1) should i call Macro 2 for the implementation and check.

I want the TestForDuplicates code to check with sheet 3, Sells, and check in Column A if the barcode exists. If it those, send back the message error and not register the input. If the barcode is not duplicated on Column A sheet 3, continue with Macro 2 and copy the information of Sheet 1 inventory related to the barcode into the sheet 3, sells.

 

[jkpieterse]

You already have code in there which does almost what you need, except it does so in Tabelle2:

    Set rng = Tabelle2.Columns("A:A").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    
        If rng Is Nothing Then

So you would declare a second range variable rng2 and do a Find against Tabelle3 in the right column and if rng2 is found you know it is already there.

By the way: This statement is not needed as the code will end up there anyway:

 GoTo ende

 

[Katolux]

You already have code in there which does almost what you need, except it does so in Tabelle2:

    Set rng = Tabelle2.Columns("A:A").Find(what:=barcode, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)

   
        If rng Is Nothing Then

So you would declare a second range variable rng2 and do a Find against Tabelle3 in the right column and if rng2 is found you know it is already there.

By the way: This statement is not needed as the code will end up there anyway:

 GoTo ende

Thanks for the answer jkpieterse. The first code sends a message back if the Code is not found.
The sencond range should be stablished in the opposite way, correct?
So it first checks in tabelle 2 that the value exists and then on Tabelle 3 that is not there yet before doing the whole process?
I am super rusty and a little bit lost on how to implement both together, as I keep getting an error or it directly ignores the second Range.

 

[jkpieterse]

I would guess something like this:

Sub receive()
    Dim barcode As String
    Dim rng As Range
    Dim rng3 As Range
    Dim rown, lrow As Long
    Dim qty As Long


    barcode = Tabelle1.Cells(2, 3)
    Tabelle2.Activate
    'is there a barcode
    If barcode = "" Then Exit Sub
    If barcode <> "" Then

        Set rng = Tabelle2.Columns("A:A").Find(what:=barcode, _
                                               LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                               searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)


        If rng Is Nothing Then
            MsgBox "Barcode nicht gefunden"
            GoTo ende
        Else
            Set rng3 = Tabelle3.Columns("A:A").Find(what:=barcode, _
                                                    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                                    searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not rng3 Is Nothing Then
                MsgBox "Barcode schon gefunden in Tablle3"
            Else
                rown = rng.Row

                If barcode = "" Then Exit Sub
                If barcode <> "" Then

                    Tabelle2.Cells(rown, 8).Value = Tabelle2.Cells(rown, 8).Value

                    Tabelle2.Range(Cells(rown, 2), Cells(rown, 7)).Copy
                    Tabelle3.Activate

                    lrow = Tabelle3.Cells(Rows.Count, 2).End(xlUp).Row + 1
                    Tabelle3.Cells(lrow, 2).PasteSpecial

                    Tabelle3.Cells(lrow, 1).Value = barcode

                    Tabelle3.Cells(lrow, 8) = Date & "  " & Time
                    Tabelle3.Cells(lrow, 8).NumberFormat = "m/d/yyyy h:mm"
                    MsgBox "Registered"
                End If

            End If
        End If

    End If
    'turn off the marching ants
    Application.CutCopyMode = False
    Tabelle1.Activate

    Tabelle1.Cells(2, 3).ClearContents

    ActiveWorkbook.Sheets("scan").Activate
    Sheets("scan").Range("C2").Select    '(and activate)
End Sub

 

[Katolux]

That works as a charm. thank you so much, was declaring it in the wrong place