VBA Overflow error 6 on excel barcode scanning sheet?

Question

Running a barcode tracker to track in and out times when scanning a barcode with a Zebra scanner. On the macro module, I'm getting an overflow 6 error when scanning a barcode in. The barcode comes in as never ending number symbols but when clicking into the cell itself, shows the 12 digit barcode number.

The line of code it is taking me to debug is:

barcode = Worksheets("Sheet1").Cells(2, 2)

This is the full code:

Sub inout()
Dim barcode As String
Dim rng As Range
Dim rownumber As Long

barcode = Worksheets("Sheet1").Cells(2, 2)
If barcode <> "" Then
  Set rng = Sheet1.Columns("a:a").Find(What:=barcode, _
  LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
  SearchDirection:=xlnext, MatchCase=False, SearchFormat:=False
  If rng Is Nothing Then
again:
  ActiveSheet.Columns("a:a").Find("").Select     
  ActiveCell.Value = barcode
  ActiveCell.Offset(0, 1).Select
  ActiveCell.Value = Date & " " & Time
  ActiveCell. NumberFormat = "m/d/yyyy h:mm AM?PM"
  Worksheets("Sheet1".Cells(2,2) = ""
 Else
  rownumber = rng.Row
  Sheet1.Cells(rownumber, 3). Select
  ActiveCell.Interiot.ColorIndex = 4
  If Worksheets("Sheet1").Cells(rownumber, 3).Value <> "" Then GoTo 
again
  Worksheets("Sheet1").Cells(rownumber, 1).Select
  ActiveCell.Offset(0, 2).Select
  ActiveCell.Value = Date & " " & Time
  ActiveCell.NumberFormat - "m/d/yyyy h:mm AM/PM"
  Worksheets("Sheet1").Cells(2, 2) = ""

  End If
End If

End Sub

Would anyone know how to fix this, please?

Trying to scan in barcode to enter in and out time. Barcode came in as never ending number symbols with an

overflow 6 error

in vba.

Answer 1

Try this out - comments inline:

Sub inout()
    Dim barcode As String, rng As Range, f As Range, ws As Worksheet, newrow As Boolean

    Set ws = Sheet1

    barcode = Trim(ws.Cells(2, 2).Value)
    If barcode <> "" Then
        
        Set rng = ws.Columns(1) 'column with barcodes
        'find the *last* instance of the barcode in ColA (if it exists)
        Set f = rng.Find(What:=barcode, LookIn:=xlFormulas, LookAt:=xlWhole, _
                  after:=rng.Cells(1), MatchCase:=False, SearchDirection:=xlPrevious)
        
        newrow = f Is Nothing 'need a new row if barcode not found
        If Not newrow Then newrow = Len(f.EntireRow.Cells(3).Value) > 0 '...or if found but "in" is already populated
        
        If newrow Then 'add new row?
            Set f = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
            f.Value = barcode
        End If
        TimeStamp f.Offset(0, IIf(newrow, 1, 2)) 'add "out" or "in" timestamp
          
        ws.Cells(2, 2).ClearContents
    End If

End Sub

'format a cell and add a timestamp
Sub TimeStamp(c As Range)
    With c
        .NumberFormat = "m/d/yyyy h:mm AM/PM"
        .Value = Now
    End With
End Sub