PDA

View Full Version : [SOLVED] Excel Macro help - If target cell in range of cells.



ullrichr
12-16-2019, 09:41 AM
Ok, a novice...

Here is a script I'm working on. It is for a barcode scanner. When they scan a bar code it places it in the Excel selected and enter date and time info in col B, C, and D
It work for the most part but I'm trying to have it check to see if the barcode value placed in the cell is in col A of another worksheet ("Student Roster") to make sure it is a valid entry. If it's not valid clear the cell before it enters the other data and give an error beep. If it is valid play a successful "beep" and enter the date and time info.

Any suggestion would be great.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
'how to?
If Target.Value = Range("A:A") Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
End If
Set tCell = rCell.Offset(0, 2)
If IsEmpty(tCell) Then
tCell = Int(Now)
tCell.NumberFormat = "dddd mmm dd, yyyy"
End If
Set tCell = rCell.Offset(0, 3)
If IsEmpty(tCell) Then
'how to pull just the time
tCell.NumberFormat = "hh:mm:ss AM/PM"
End If
Next
'rest of the IF
Else
Beep
End If
End If
End Sub

p45cal
12-17-2019, 08:01 PM
try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Dim myTime As Date

Set rInt = Intersect(Target, Range("A:A"))
If Not rInt Is Nothing Then
'just in case you're doing this at midnight
myNow = Now
myDate = Int(myNow)
myTime = myNow - myDate

On Error GoTo errHandler 'only enable when finished debugging. (Speeds up)
Application.EnableEvents = False 'only enable when finished debugging.
For Each rCell In rInt
If Not IsError(Application.Match(rCell.Value, Sheets("Student Roster").Range("A:A"), 0)) Then
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = myNow
tCell.NumberFormat = "mmm dd, yyyy hh:mm:ss AM/PM"
End If

Set tCell = rCell.Offset(0, 2)
If IsEmpty(tCell) Then
tCell = myDate
tCell.NumberFormat = "dddd mmm dd, yyyy"
End If

Set tCell = rCell.Offset(0, 3)
If IsEmpty(tCell) Then
tCell = myTime
tCell.NumberFormat = "hh:mm:ss AM/PM"
End If
Else 'not a match in student roster:
Application.EnableEvents = False 'must be present here if not elsewhere to prevent infinite looping.
rCell.ClearContents
Beep
End If
Next
'Else 'this Else line not needed, not a change in column A
End If
errHandler:
Application.EnableEvents = True
End Sub