Enter Query in Column(A) and get the result in Column(B) by Lookup function using VBA

Question :

Please, you can help me with one VBA Code which is able to extract from two columns A, B....B value. But how?

If I have database în sheet 2:

(col.A) 1. Jhon..... 30 years old(col. B)
(col.A) 2. James..... 32 years old(col. B)
(col.A) 3. Bob..... 35 years old(col. B)
.............................................................
(col.A) n. John..... 39 years old(col. B)

And I want în sheet 1:

When I type în col.A for ex: "James", în col.B gives "32 years old".....then aleatory typed "John" în col.A and în col B give "39 years old" etc.

Can you help me with that VBA Code?

Thank you,

Solution: 

Let's first making our Database Dynamic.

Sheets("date") has the data which you want to look into for your query.

1. Go to Ribbon --> Formulas --> Name Manager --> Click on button NEW
2. in New box, you are asking for new range details.
3. The name is as "Database"
4. Now, in "referes to": paste the following formula and save it and close that.

=OFFSET(date!$A$2,0,0,COUNTA(date!$A:$A)-1,2)

5. now, open VBA Compiler
6. Open Project Window
7. under Microsoft Excel Objects find the Sheet where you want to insert the query and get the result in the same.

In your case, Sheets("vlookup") is your query sheet.

8. Click on Sheet1(vlookup) and the coding area will be open up.
9. Now you want to change the value automatically after entering the value in the specific cell, So, in this case, we required "Worksheet_Change" procedure to fire up the result.
10. Select the Worksheet Object and then select the "Change" Procedure
11. You will get the,

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Now insert the following code in between above two lines.
=======================
If Target.Column = 1 Then
Application.EnableEvents = False
With THEVL.Sheets("vlookup")

.Activate
.Select
.Cells(Target.Row, 2).Value = "=IFERROR(VLOOKUP(A" & Target.Row & ",Database_v,2,FALSE),0)"
End With
Application.EnableEvents = True

End 

========================

==

12. Save the Excel and restart the application.
13. Try to add something to anywhere in Column(A) of a sheet("vlookup"). you will get the result in adjacent to the cell.

I hope it helps you and other folks.

Thank you.
- Kamal Bharakhda