Userform & VLOOKUP to populate fields

ccollier

New Member
Joined
Jun 21, 2011
Messages
2
I am new to Userforms, but have successfully created one where all entries work - except one. I am creating a Userform for a student directory. If I create a user as NEW, it works fine. However, I have information from previous years that just needs to be updated, not NEW. This information resides in a workbook tab named "Combine".

User form is set up like this:
Text Box - txtstudent_number
- user will enter Student Number
Command Box - cmd_lookup_studentID
- user will CLICK button to look up information in Excel workbook "Combine"
If the txtstudent_number makes a match in column A in "Combine", pull First Name, Last Name, Address, City, State, Zip into workbook.
- user can update the information and then click update.

I have done lots of searches to try to create the code for the Command Box, with no luck. This is what I have today to bring in the firstname (understanding that I will need to add code to bring in the other fields):

Command Box - cmd_lookup_studentID CODE:

Private Sub cmd_lookup_studentID_Click()
Dim Studentnumber As String
Studentnumber = txtstudent_number
Dim Range As Range
Set Range = Worksheets("Combine").Range("A1:AE1")
txtstudent_firstname.Value = Application.WorksheetFunction.VLookup(Studentnumber, Range, 2, 0)

End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ccollier

New Member
Joined
Jun 21, 2011
Messages
2
I'm still trying to work through this. I've searched through lots of info and changed up the code. Still not working. I'm not sure why the VLOOKUP code isn't right. Any assistance is appreciated.


CODE:


Private Sub cmd_lookup_studentID_Click()
Dim Range As Range, ID As Range, res As Variant
Set Range = Worksheets("Combine").Range("A3:AI500").Columns(35)
Student_Number = Application.Match(txtstudent_number.Text, Range, 0)
If Not IsError(res) Then
Set ID = Range(res)
txtstudent_firstname.Text = Application.VLookup(Student_Number, Range, 2, 0)
txtstudent_lastname.Text = Application.VLookup(Student_Number, Range, 3, 0)
Else
MsgBox "Not found in database"
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,591
Members
412,328
Latest member
Mrcaff88
Top