Option Explicit Sub check_id() On Error GoTo ErrorHandler Dim name As String Dim i As Integer Dim rowls As Integer Dim Department As String Dim eMail As String ID = Range("H1") name = Range("H2") eMail = Range("H3") Department = Range("H4") name = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 2, False) eMail = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 3, False) Department = Application.WorksheetFunction.VLookup(ID, Sheet1.Range("A2:D1002"), 4, False) rowls = Cells(Rows.Count, 2).End(x1up).Row For i = 1 To rowls If Application.WorksheetFunction.VLookup(ID, Range("A2:D1001"), 2, False) = Cells(i, 2) Then MsgBox "You have been registered beforehand " Else MsgBox "Not Ok" ' Never invoked End If Next i Exit Sub ErrorHandler: Response = MsgBox("You have not registered an account yet please approach the administrator.", vbOKOnly, "Unknown user") If Response = vbOK Then Mainpage.Show ElseIf Response = vbCancel Then Mainpage.Show End If End Sub
Hi guys attached above is my code i am trying to conduct a vlookup in vba however im unable to get excel to return the value into the specific cells namely H2,H3,H4. H1 is the value that im searching for. Im also trying to make it search through a list of items hence if the value in cell H1 is not in the list a msgbox appears and upon pressing ok or cancel it returns to the mainpage. Do let me know if theres any errors awaiting your reply and thank you in advance!