Code
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
Display More
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!