Need help please. I cant seem to make the ElseIf line work correctly. I scan into cell A1 and next row and correct columns are populated. Its when I try and scan again, supposed to find value in column A. If already there then time stamp goes into column F. If column A and F on same row have values in them then a new row is started. This last bit I haven't written yet because I have not corrected the column F portion.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FindString As String
Dim Rng As Range
Dim bcr As Range
Dim nr As Long
Dim nc As Long
Dim n As Long
Dim irow As Long
Dim Standard As String
Dim Description As String
Dim Response As Integer
Set ws = Worksheets("Report")
If Target <> Range("A1") Then
MsgBox ("You can only scan Barcodes into Sheet3 range A1")
ActiveCell.ClearContents
Range("A1").Select
End If
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
'Find cell A1 value in column B and populate sheet 4 report
FindString = Sheets("StandardTable").Range("A1")
If Trim(FindString) <> "" Then
With Sheets("StandardTable").Range("B2:B500")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End If
'Collect Standard Information.
If Rng Is Nothing Then Response = MsgBox("Do You Want To Add Standard?", vbYesNo, "Add Standard")
If Response = vbYes Then
'End If
Standard = InputBox("Enter Standard ID.", "MPD Number")
With Sheets("StandardTable")
NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("C" & NextRow) = Standard
End With
Description = InputBox("Enter Standard Description.", "Description")
With Sheets("StandardTable")
NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("D" & NextRow) = Description
End With
ID = InputBox("Scan RFID Tag Now.", "Standards RFID Tag")
With Sheets("StandardTable")
NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("B" & NextRow) = ID
End With
MsgBox "Scan Standard RFID Again", vbOKOnly
UserForm1.Show
End If
If Response = vbNo Then
MsgBox "Standard Not Found"
End If
' Code works up to here as expected!!!!!!!!
'Find A1 value from Standards Table in Report column A and populate columns ID, description and Dates to Report
With ws
irow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 0
On Error Resume Next
ws.Cells(irow, 1).Value = Application.Index(Range("C2:C500"), Application.Match(Range("A1").Value, Range("B2:B500"), 0))
ws.Cells(irow, 2).Value = Application.Index(Range("D2:D500"), Application.Match(Range("A1").Value, Range("B2:B500"), 0))
On Error GoTo 0
End With
n = Application.CountIf(ws.Columns(1), Target.Value)
If n = 0 Then
nr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 0
If nr = 2 And ws.Range("A2") = vbEmpty Then
nr = 1
End If
ws.Range("A" & nr) = ws.Cells(irow, 1).Value
ws.Range("B" & nr) = ws.Cells(irow, 2).Value
ws.Range("E" & nr) = Now()
ws.Range("E" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
ElseIf n > 0 Then
nr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 0
If ws.Cells(nr, 5).Value = Target.Value Then
nc = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
ws.Cells(5, nc) = Now()
ws.Cells(5, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
'End If
Else
Set bcr = ws.Columns(1).Find(Target.Value, LookAt:=xlWhole)
nc = ws.Cells(bcr.Row, Columns.Count).End(xlToLeft).Column + 1
ws.Cells(bcr.Row, nc) = Now()
ws.Cells(bcr.Row, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
End If
End If
End With
With Sheets("StandardTable").Range("A1")
.ClearContents
UserForm1.Show
End With
With Application
'Sheets("Report").Save
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
