ccarrillo54
New Member
- Joined
- Nov 29, 2018
- Messages
- 2
Hello, I'm trying to create a check in/out system at a lab I work at. I'm not to experienced and using VBA. I was able to tinker with some formulas to get it to do what I wanted, but I wasn't fully successful in getting all the steps I wanted done. so what I'm trying to do is check in samples using a barcode followed by a date in the cell right next to it. I want this formula to apply to A2000 so I can check in multiple samples. im using an input box and I want this input box to be able to detect matched samples and place them in the checked out column "C" followed by a date in the cell right next to it. I would appreciate any help you guys can give me. Here's the code I am currently using.
Private Sub Worksheet_Activate()
Dim myValue As Variant
Dim code As Variant
Dim matchedCell As Variant
myValue = InputBox("Please scan a barcode")
Range("A2").Value = myValue
Set NextCell = Cells(Rows.Count, "A").End(xlUp)
If NextCell.Row > 1 Then NextCell = NextCell.Offset(1, 0)
Set matchedCell = Range("a2:a2000").Find(what:=code, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If myValue = True Then Paste ("C2;C2000")
If Not matchedCell Is Nothing Then matchedCell.Offset(-1, 1).Value = Now
End Sub
A B C D
[TABLE="width: 644"]
<colgroup><col width="215" style="width: 161pt;" span="4"> <tbody>[TR]
[TD="width: 215, bgcolor: gray"]CHECKED IN[/TD]
[TD="width: 215, bgcolor: gray"]DATE[/TD]
[TD="width: 215, bgcolor: gray"]CHECKED OUT[/TD]
[TD="width: 215, bgcolor: gray"]DATE[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]4000-18-1128-1[/TD]
[TD="bgcolor: #E7E6E6"]11/29/2018 11:03[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
</tbody>[/TABLE]
Private Sub Worksheet_Activate()
Dim myValue As Variant
Dim code As Variant
Dim matchedCell As Variant
myValue = InputBox("Please scan a barcode")
Range("A2").Value = myValue
Set NextCell = Cells(Rows.Count, "A").End(xlUp)
If NextCell.Row > 1 Then NextCell = NextCell.Offset(1, 0)
Set matchedCell = Range("a2:a2000").Find(what:=code, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If myValue = True Then Paste ("C2;C2000")
If Not matchedCell Is Nothing Then matchedCell.Offset(-1, 1).Value = Now
End Sub
A B C D
[TABLE="width: 644"]
<colgroup><col width="215" style="width: 161pt;" span="4"> <tbody>[TR]
[TD="width: 215, bgcolor: gray"]CHECKED IN[/TD]
[TD="width: 215, bgcolor: gray"]DATE[/TD]
[TD="width: 215, bgcolor: gray"]CHECKED OUT[/TD]
[TD="width: 215, bgcolor: gray"]DATE[/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"]4000-18-1128-1[/TD]
[TD="bgcolor: #E7E6E6"]11/29/2018 11:03[/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[TD="bgcolor: #E7E6E6"] [/TD]
[/TR]
</tbody>[/TABLE]