Comparing Two Lists of ID Numbers in Excel [Solved/Closed]
digitiser1
Posts Tuesday October 16, 2012Registration date
October 16, 2012
Last seen
-
Oct 16, 2012 at 10:12 AM - Latest reply: rizvisa14481
Posts Thursday January 28, 2010Registration dateContributorStatus
January 6, 2016
Last seen
- Oct 28, 2012 at 12:03 PM
I have a list of student ID numbers which I would like to use as a student check-in. Column A is the numbers, Column B and Column C are first and last names respectively. I want to store this data on one sheet, let's call it rosters and set up another sheet, called signin to pull info from there when they sign in.
When a student scans their ID, it pulls their ID number and enters it into a cell. I would like to create a macro that creates a timestamp in Column B corresponding to the time of check-in, then finds the matching ID number in the sheet called rosters and pulls their first and last name from that sheet and enters those into Columns C and D.
I hope that someone can help me with this, I am fairly inexperienced with VBASIC.
this macro is based on assumption that "Rosters" is the name of the sheet where all names are written
1. open the excel file
2. press alt + f11 (alt and f11 keys at same time) to launch VBE
3. in VBE, click on the sheet where id would be entered
4. Paste the code given
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim idRange As Range
Dim idCell As Range
Dim matchRow As Long
Dim rosterSheet As String
rosterSheet = "Rosters"
Set idRange = Intersect(Range("A:A"), Target)
If (idRange Is Nothing) Then Exit Sub
Application.EnableEvents = False
For Each idCell In idRange
If (idCell.Value = vbNullString) Then
idCell.Offset(0, 1) = vbNullString
idCell.Offset(0, 2) = vbNullString
idCell.Offset(0, 3) = vbNullString
Else
idCell.Offset(0, 1).Value = Format(Now, "yyyy-mm-dd hh:nn:ss")
matchRow = getItemLocation(idCell.Value, Sheets(rosterSheet).Columns(1), True, False, True)
If (matchRow = 0) Then
idCell.Offset(0, 2) = "** UNKNOWN **"
idCell.Offset(0, 3) = "** UNKNOWN **"
Else
idCell.Offset(0, 2) = Sheets(rosterSheet).Cells(matchRow, "B")
idCell.Offset(0, 3) = Sheets(rosterSheet).Cells(matchRow, "C")
End If
End If
Next
Application.EnableEvents = True
End Sub
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
has helped 1882 users this month