Card Reader - Excel VBA Help

[WWII_Buff]

Hello all!

I have an event that require card access to validate attendance.
Note: The card scanner populates cell B2 and overwrites the same cell after every scan.

[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID SCANNER[/TD]
[TD][/TD]
[TD]SCANNED[/TD]
[TD]ID[/TD]
[TD]NAME[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]
BURNO[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]9000[/TD]
[TD]JAMES[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5000[/TD]
[TD]ADAM[/TD]
[/TR]
</tbody>[/TABLE]


When there is an ID match after the scan, I would like "Y" added in the SCANNER column then copy and paste as value within the same cell.

Philwojo gave me a formula to use -

=IF(ISERROR(VLOOKUP(E6,$B$4,1,0)),"","Y")

Note: multiple scans are also possible.

I am completely stumped on how to make this work. Any ideas and help is appreciated.

 

[MartyS]

Hi!

Could you expand on:

The card scanner populates cell B2 and overwrites the same cell after every scan.

versus:

multiple scans are also possible

If the card scanner populates cell B2 and overwrites the cell after every scan... what does "multiple scans are possible" mean?

Does that mean it populates B3.. B4.. B5.. onwards? Or that it scans and writes to cell B2, then immediately overwrites that cell with the next card number?? Or something else?

 

[WWII_Buff]

Hi!

Could you expand on:



versus:




If the card scanner populates cell B2 and overwrites the cell after every scan... what does "multiple scans are possible" mean?

Does that mean it populates B3.. B4.. B5.. onwards? Or that it scans and writes to cell B2, then immediately overwrites that cell with the next card number?? Or something else?

Sorry for the confusion and thank you for your response.

* I was just staying that the card scanner populates cell B2 and overwrites the cell after every scan.
* multiple scans are also possible - meaning an individual can scan in multiple times - but I don't need to track how many times the individual card is scanned.

 

[MartyS]

Ok.. it's kind of obvious now you've spelled it out! ha ha! Thank you for the additional information.

 

[WWII_Buff]

Ok.. it's kind of obvious now you've spelled it out! ha ha! Thank you for the additional information.

To your defense - knowing when to be specific can be a challenge for me so you are quite in-line with pretty much everyone I know (both reality and virtually) LOL.

 

[MartyS]

Thank you!

Ok.. maybe try this?

https://www.dropbox.com/s/xkm2f90ceihlwpc/Card Reader - Excel VBA Help.xlsm?dl=0


I added an extra bit of code / column in there so that if a card is read and it's NOT on the list, it appears in this new column [NOT FOUND].

If it is found, it just puts a "Y" next to the number.


Here's the code.

N.B. You'll notice it's a Worksheet_Change routine, and needs to be put into the WORKSHEET itself and NOT a Module.

Private Sub Worksheet_Change(ByVal Target As Range)


    Const DataStartRow = 2


    Dim RowNum As Long
    Dim LastRow As Long
    
    If Target.Cells.Address = "$A$2" Then
    
        Application.EnableEvents = False
    
        LastRow = ThisWorkbook.Sheets("Sheet1").Range("D:D").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        If LastRow < 2 Then LastRow = 2
    
        On Error Resume Next
        RowNum = Application.WorksheetFunction.Match(Range("A" & DataStartRow).Value, Range("D" & DataStartRow & ":D" & LastRow), 0) + (DataStartRow - 1)
        If Err.Number <> 0 Then
            LastRow = 0
            LastRow = ThisWorkbook.Sheets("Sheet1").Range("G:G").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            On Error GoTo 0
            If LastRow < 2 Then LastRow = 2
            ThisWorkbook.Sheets("Sheet1").Range("G" & LastRow + 1).Value = ThisWorkbook.Sheets("Sheet1").Range("$A$2").Value
          Else
            On Error GoTo 0
            ThisWorkbook.Sheets("Sheet1").Range("C" & RowNum).Value = "Y"
        End If
    End If
    
    Application.EnableEvents = True


End Sub

 

[WWII_Buff]

MartyS! Thank you so much! I have stupid month end reporting today so I haven't tested this - but I wanted to you know how appreciative I am for your help with this task! I'll give you a shout when I play with it around +5 hours ray:

 

[MartyS]

You're more than welcome - let me know how you get on and if I can be of further help.

 

[WWII_Buff]

You're more than welcome - let me know how you get on and if I can be of further help.

Dude! This is awesome!!!! Works as advertised! I like your "Not Found" addition too - I was thinking about find what non-team members were going crash the event so I can invite more groups.
Way to go that "extra mile" mate! Hear ye! Hear ye! By the power vested in me, I hear by proclaim MartyS Knight of the MrExel Forum! Bequest upon him all privileges and honors that go along with this title! (...which isn't more than a really deeply felt "Thank you!" )

Thank you again!

 

[MartyS]

haha! Thank you for your kind words... Glad to have been of help