VBA VLookup Help Please

Spencer2005

New Member
Joined
Oct 20, 2019
Messages
5
Hi
I’m hoping someone can help. I have a basic understanding of Excel but no knowledge of VBA.
I’m working on a school project where I have compiled a list of students and the course they are attending.
Worksheet 1 has a dropdown list containing student names in column D. When a name is selected, I want the adjacent cell in column E to populate with the course they are attending.
Worksheet 2 contains the student name in column A and the course name in column B.
I’m currently using VLookup to retrieve the info, but would prefer to use VBA. Can anyone help me with a code?
Thanks in advance!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
We need the data lay out, can you attach a link to an Excel sample file .
 
Upvote 0
Hi,

Thanks for responding - stupidly, I didn't think to give an example .

Here's a very basic mock up of how the sheets look:

Sheet 1 - My VLookup formula in column 5 for the 1st example (Sofia Alba D2) is =IFERROR(VLOOKUP(D5,Sheet2!$A$2:$B$14,2),"")
I'd like a code so that if I enter John Perda in Sheet 1, D5, the adjacent cell (E5) will automatically populate with 'Banking and Finance'

Date Raised
Query Contact IDStudentCourse
21/10/2019Lorem ipsum dolor sit amet376542Sofia AlbaPsychology
21/10/2019Fusce posuere, magna sed pulvinar 987654Jane BreenDigital Business
21/10/2019Pellentesque habitant morbi123456Lucy FieldEngineering
21/10/2019Fusce posuere, magna sed pulvinar
654321

<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3008;"><colgroup><col width="212" style="width: 159pt; mso-width-source: userset; mso-width-alt: 6784;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2272;"><colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3168;"><colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 4416;"><tbody>
</tbody>

Sheet 2 is a list of students and the course attended

Student Course
John PerdaBanking and Finance
Jane BreenBiology
James FrenchBiomedical Science
Paul SinclairBusiness
Hannah AllanDigital Business
Raoul ShahEconomics
Lucy FieldEngineering
Amelia EarleElectrical Engineering
Sofia AlbaFinancing
Emma JohnsGeography
Steve PaceHuman Geography
Michael DaviesLaw
Pablo SanchezPsychology
<colgroup><col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 3808;"> <col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 4512;"> <tbody> </tbody>


Hope someone can help.

Many thanks
 
Upvote 0
@Spencer2005
Please do not keep duplicating your posts. Sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.
 
Upvote 0
If you want col E to fill automatically, you are better off with a formula, rather than VBA.
Is there any particular reason why you want a macro?
 
Upvote 0
If you want col E to fill automatically, you are better off with a formula, rather than VBA.
Is there any particular reason why you want a macro?

You're right - the formula works great but it's a project with a few fellow students entering data in the workbook and the formula keeps getting deleted or altered.
I protected column E so the entry and the formula couldn't be deleted ( the entry in Col. E would only be deleted if the name in column D was deleted).
Unfortunately, we aren't allowed to password protect shared workbooks so it was just being unprotected and messed up again.
I just thought a macro would take away the possibility of the action going wrong.

Apologies again for the duplicate posting - I'll definitely be more patient
 
Upvote 0
Ok, how about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Fnd As Range
    
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
        If Not Fnd Is Nothing Then Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
    End If
End Sub
This needs to go in the Sheet1 code module
 
Upvote 0
I thought I'd recorded it correctly but when I enter a name in column D, nothing happens and there's no error message.

Can you tell me what I've done wrong from this screenshot?

Many thanks
 
Last edited by a moderator:
Upvote 0
Oops, my mistake I was looking at the wrong column. Delete the previous code & try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Fnd As Range
    
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        Set Fnd = Sheets("Sheet2").Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
        If Not Fnd Is Nothing Then Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
    End If
End Sub
Also you cannot post images to the site. You would need to upload to a share site & post a link
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top