Hi Sam,
i would just use Find Method.

Paste code behind your form & adjust as needed.
I am assuming you have a button on form to call procedure that student
presses to search for RollNo?
And textboxes are still named TextBox1, TextBox2 etc etc?

Hope useful

Sub GetStudentData()

Dim WS2 As Worksheet
Dim Search As String
Dim Foundcell As Range

Set WS2 = Worksheets("Sheet2") '<< change name as required


'searching for rollno
Search = Me.TextBox1.Text

Set Foundcell = WS2.Columns(1).Find(Search, _
LookIn:=xlValues, _
LookAt:=xlWhole)


If Foundcell Is Nothing = False Then

'found search value
For na = 2 To 5

Me.Controls("TextBox" & na).Text = _
Foundcell.Offset(0, na - 1).Value

Next na

Else
'did not find search value
'tell user
msg = MsgBox(Search & " Not Found!", 16, "Search")

End If


End Sub
--
jb


"sam" wrote:

Hi All,

How can I use Inded and Match function on VBA to populate certain form fields?

For eg: I have a userform where Users input:

Roll No(Unique to every student):
Last Name :
First Name :
Subject1 :
Ph No:

What I want to do is: Once Users Input Their Roll No, I want to populate
their Last Name, First Name, Subject1 and Ph No. Also the roll number might
not be in descending order, Hence I want to use somethign like Index and
Match function, So once a student Inputs the roll number, It will match the
roll number and populate other fields respectively

The button to launch the Form is on Sheet1 and All the student data is in
Sheet2.

Hope I made it clear

Thanks in Advance