Hello,
Private Sub CommandButton1_Click()
Dim orange As Range, acell As Variant, bcell As Variant, ccell As Variant
Dim ws As Worksheet
Dim fin As Range
Dim result As Range
Dim lastrow As Range
Dim searchstring As String
Dim foundat As String
Dim cell As Variant
Dim cel As Variant
Dim myTable As ListObject
Dim myArray As Variant
Dim xa As Variant
Dim ya As Variant
On Error Resume Next
Set lastrow = Worksheets("sheet2").Range(Rows.Count, "A").End(xlDown).row.Value
For row = 2 To 3
Set ws = Worksheets("sheet2")
Set orange = ws.Cells(row, 2)
searchstring = ws.Cells(row, 1).Value
col = 1
ws.Cells(row, 1) = Application.inputbox(prompt:="Enter student's full name", Title:=" Hello ", Type:=(2))
If Application = Cancel Then
Stop
Close
Exit Sub
result = ws.Cells(row, 3)
'Set path for Table variable
Set myTable = ActiveSheet.ListObjects("Table1")
'Create Array List from Table
myArray = myTable.DataBodyRange.Select
'Loop through each item in Third Column of Table (displayed in Immediate Window [ctrl + g])
For Each cell In myArray
For Each cel In myArray
Set acell = myTable.listrange(Cells(row, 1)).DataBodyRange.Select.Find(what:=cell, after:=Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
Set bcell = myTable.listrange(Cells(row, 2)).DataBodyRange.Select.Find(what:=cel, after:=Cells(1, 2), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False, searchformat:=False)
If cell = cel Then
MsgBox "student is in " & result
Else
MsgBox "not found"
End If
Next
Next
End If
Next
End Sub
