Userform & VLOOKUP to populate fields

[ccollier]

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

 

[ccollier]

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