Match cell value to range

[StvOne]

Hi, Can anyone help with this code? I cant seem to make it work correctly. I am trying to find cell (A1) value's match in range ("B:B"). And then populate sheet 4 (report) column D with the corresponding username found in sheet 2 Associate Table. I have a table, sheet 2 which has cell A1 RFID scanned input. Column B has RFID badge numbers. Column C has the username that is associated to the badge number. Reason for this is the RFID tags I have are preprogrammed numbers and I did not want to buy a read write head. If found the username will populate sheet 4 report column D next available row. If not found then input box prompt asks for user input to create username and badge ID. this part works!! Thank you in advance for any insight.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim irow As Long
    Dim ws As Worksheet
    Dim C As Range
'    Dim V As Variant
    Set ws = Worksheets("Report")
If Target <> Range("A1") Then
  MsgBox ("You can only scan Barcodes into Sheet2 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
For Each C In Range("A1")
    If IsNumeric(Application.Match(C, Range("B2:B100"), 0)) Then
        
'find first empty row in Report
 End With
    irow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
' copy the  scan to report
    
    With ws
    .Cells(irow, 4).Value = Sheets("AssociateTable").Range("A1") ' This value should be column C (username)
    'that is a match to the cells found value.
   End With
    With Sheets("AssociateTable").Range("A1")
    .ClearContents
    .Select
    
Else:
'Collect user name.
    userName = InputBox("Enter your first and last name.", "Name")
            With Sheets("AssociateTable")
                NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
                .Range("C" & NextRow) = userName
End With
    ID = InputBox("Scan ID Badge Now.", "Badge ID")
            With Sheets("AssociateTable")
                NextRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
                .Range("B" & NextRow) = ID
End With
    MsgBox "Scan Associate Badge Again"  'Only if badge was not found should this message appear
   End With
End Sub

 

[StephenCrump]

I cant seem to make it work correctly.

I am guessing that you're referring to this bit of code?

For Each C In Range("A1")
    If IsNumeric(Application.Match(C, Range("B2:B100"), 0)) Then
    'find first empty row in Report
    irow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    ' copy the  scan to report
    With ws
       .Cells(irow, 4).Value = Sheets("AssociateTable").Range("A1") [B]' [COLOR=#ff0000]This value should be column C (username)[/COLOR][/B]
[COLOR=#ff0000][B]    'that is a match to the cells found value.[/B][/COLOR]
    End With

In which case, perhaps:

irow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

On Error Resume Next
ws.Cells(irow, 4).Value = Application.Index(Range("C2:C100"), Application.Match(Range("A1").Value, Range("B2:B100"), 0))
On Error GoTo 0

 

[StvOne]

StephenCrump, Thank you. Your suggestion worked as expected!! I am now one big step closer to completing this project. Again, thank you...