Card Reader - Excel VBA Help

WWII_Buff

Board Regular
Joined
Nov 13, 2017
Messages
88
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.

ABCDEF
1ID SCANNERSCANNEDIDNAME
290008000
BURNO
3Y9000JAMES
45000ADAM

<tbody>
</tbody>


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 -
Code:
=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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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. :oops:
 
Upvote 0
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.



Code:
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
 
Upvote 0
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 (y):pray:(y)
 
Upvote 0
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! o_OHear 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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top