Menu
Ask a question Report

Comparing Two Lists of ID Numbers in Excel [Solved/Closed]

Posts
1
Registration date
Tuesday October 16, 2012
Last seen
October 16, 2012
 - Oct 16, 2012 at 10:12 AM - Latest reply:
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 - Oct 28, 2012 at 12:03 PM
Excel Ask a question Report
Hello, thanks in advance for reading this.

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.



See more 

1 reply

Best answer
Report Respond to rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 Oct 16, 2012 at 07:55 PM
1
Thank you
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

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1699 users this month

Report
Posts
3
Registration date
Saturday October 27, 2012
Last seen
October 31, 2012
 - Oct 27, 2012 at 02:15 PM
Im trying to play with this macro, since I am trying to relearn VB, does this auto run when a cell value changes?
Report
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 - Oct 28, 2012 at 12:03 PM
To prod you to teach you to learn to "fish", I would only say try it and see if macro does work when cell value changes :)
Ask a question