Making VLOOKUP return a value into a cell in vba

Can you describe the logic of what you are trying to do, in detail, here? Trying to work out your aims by trying to decipher incorrect code is not so easy.

A fragment of your code.

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)

ID is undeclared, so the 1st statement would assign the value (.Value property) of cell H1 in the active worksheet to the Variant variable ID. The 2nd statement would assign the value of cell H2 to the string variable name. And similarly for eMail and Department. If you want cells H2:H4 replaced by the values from the VLOOKUP calls, you should replace the 2nd and subsequent lines above with something like

Dim k As Variant
k = Application.Match(ID, Worksheets("Sheet1").Range("A2:A1002"), 0)
If IsNumeric(k) Then
  With ActiveSheet
    .Range("H2").Value = Worksheets("Sheet1").Range("A2:D1002").Cells(k, 2).Value
    .Range("H3").Value = Worksheets("Sheet1").Range("A2:D1002").Cells(k, 3).Value
    .Range("H4").Value = Worksheets("Sheet1").Range("A2:D1002").Cells(k, 4).Value
  End With
Else
  MsgBox Title:="check_id macro error", Prompt:="ID [" & ID & "] not yet registered."
End If

It looks like there are other problems too, such as the For loop possibly iterating over many rows but performing the VLOOKUP call on the same ID value every iteration.

. Do not use Error Handlers until the code is finished and tested. If you do you are hiding error messages which might be usefule.

2. If you use VBA then use the VBA .Find Function

Range.Find method (Excel)

Why would you want to use VBA? Using inbuilt formulas is the most efficient way.

I have added code tags in your first post. When posting code on the Forum please use them to make reading and copying code easier.

Code Tags

Hello,

Attached is your test as a starting point ...

Hope this will help

I'm guessing you are after something like this? See attached ... Final Proto123 v2.xlsm

Quote from xiaowang

Hi carim may i know what does the res in dim ID , res mean?

Hello,

Thanks for your Thanks

res is a variable which stands for result ...

It is used to get the row number thanks to Application.Match

Hope this clarifies

Have you solved your problem ...

Or

Are you still facing an obstacle ...?

Glad you could fix your problem

Thanks for your Thanks AND for the Like

This example demonstrates using .Find which I would always recommend using in VBA.

To make it less likely that a missing or wrong ID is entered in the sheet I have used a Data Validation list in H1.

In my example I have converted the data into a Table, this makes it easier to create a Named Range for the list that will expand as data is added. It also helps in the VBA.

Onece the ID is found it simply copies the 3 adjacent cells and pastes using Transpose to H2.

Option Explicit
Sub check_id()
    Dim res
    Dim oWs As Worksheet
    Dim rCl As Range, rCopy As Range
    Dim oTbl As ListObject
    Dim ID As String

    Set oWs = Worksheets("User_Database")
    
    With oWs
        Set oTbl = .ListObjects(1)
        ID = .Range("H1")

        If Application.WorksheetFunction.CountIf(oTbl.ListColumns(1).Range, ID) < 1 Then
            MsgBox "You have not registered an account yet please approach the administrator.", vbOKOnly, "Unknown user"
            '            Mainpage.Show
        Else
            Set rCl = oTbl.ListColumns(1).Range.Find(ID)

            If Not rCl Is Nothing Then
                Set rCopy = rCl.Offset(, 1).Resize(, 3)
                rCopy.Copy
                .Range("H2").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        End If
    End With
End Sub