get the last 3 digit number of ID number of student

[Bogz17]

hi guys

i have a question about my userform i got 1 command button and 16 textbox.

the textbox 1 was the one that i use to search the data and display it to other textbox.

is it possible to textbox1 to search only the last 3 digit number instead of 8 digit? wish some can answer my question here.

thank you

 

[DanteAmor]

Something like this:

Private Sub CommandButton1_Click()
    idnum = rigth(TextBox1.Value, 3)
    Set b = Sheets("sheet2").Columns("A").Find(idnum, lokat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
    Else
        MsgBox "Does not exist"
    End If
End Sub

 

[Bogz17]

thank you sir i will try these code.

 

[DanteAmor]

Let me know if you have any doubt

 

[Bogz17]

idnum = rigth(TextBox1.Value, 3) ------------------>there was an error in here sir it said that sub or function not defined
Set b = Sheets("sheet2").Columns("A").Find(idnum, lokat:=xlWhole, LookIn:=xlValues)
If Not b Is Nothing Then
'exists
Else
MsgBox "Does not exist"
End If

 

[DanteAmor]

Sorry, try with this:

Private Sub CommandButton1_Click()
    Dim idnum As String, b As Object
    idnum = Right(TextBox1.Value, 3)
    Set b = Sheets("sheet2").Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
    Else
        MsgBox "Does not exist"
    End If
End Sub

 

[Bogz17]

here is the code for my search button sir



Dim totRows As Long, i As Long


totRows = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count


If TextBox1.Text = "" Then
MsgBox "Enter the LHID you wish to search!"
End If




For i = 2 To totRows


If Trim(Sheet1.Cells(i, 1)) <> Trim(TextBox1.Text) And i = totRows Then
MsgBox "LHID not found!"
End If


If Trim(Sheet1.Cells(i, 1)) = Trim(TextBox1.Text) Then


TextBox1.Text = Sheet1.Cells(i, 1)
TextBox2.Text = Sheet1.Cells(i, 2)
TextBox3.Text = Sheet1.Cells(i, 3)
TextBox4.Text = Sheet1.Cells(i, 4)
TextBox5.Text = Sheet1.Cells(i, 5)
TextBox6.Text = Sheet1.Cells(i, 6)
TextBox7.Text = Sheet1.Cells(i, 7)
TextBox8.Text = Sheet1.Cells(i, 8)
TextBox9.Text = Sheet1.Cells(i, 9)
TextBox10.Text = Sheet1.Cells(i, 10)
TextBox11.Text = Sheet1.Cells(i, 11)
TextBox12.Text = Sheet1.Cells(i, 12)
TextBox13.Text = Sheet1.Cells(i, 13)
TextBox14.Text = Sheet1.Cells(i, 14)
TextBox15.Text = Sheet1.Cells(i, 15)
TextBox16.Text = Sheet1.Cells(i, 16)
Exit For
End If


Next i

 

[DanteAmor]

I made the changes based on your data

Private Sub CommandButton1_Click()
    Dim idnum As String, b As Object, i As Double
    idnum = Right(TextBox1.Value, 3)
    Set b = Sheet1.Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
        i = b.Row
        TextBox1.Text = Sheet1.Cells(i, 1)
        TextBox2.Text = Sheet1.Cells(i, 2)
        TextBox3.Text = Sheet1.Cells(i, 3)
        TextBox4.Text = Sheet1.Cells(i, 4)
        TextBox5.Text = Sheet1.Cells(i, 5)
        TextBox6.Text = Sheet1.Cells(i, 6)
        TextBox7.Text = Sheet1.Cells(i, 7)
        TextBox8.Text = Sheet1.Cells(i, 8)
        TextBox9.Text = Sheet1.Cells(i, 9)
        TextBox10.Text = Sheet1.Cells(i, 10)
        TextBox11.Text = Sheet1.Cells(i, 11)
        TextBox12.Text = Sheet1.Cells(i, 12)
        TextBox13.Text = Sheet1.Cells(i, 13)
        TextBox14.Text = Sheet1.Cells(i, 14)
        TextBox15.Text = Sheet1.Cells(i, 15)
        TextBox16.Text = Sheet1.Cells(i, 16)
    Else
        MsgBox "LHID not found", vbExclamation
    End If
End Sub

 

[Bogz17]

thank you for your effort and time sir but it tells it cant find the data sir in the worksheet when i search the last 3 digit number..

 

[DanteAmor]

I assume you have numeric data in the cell, then try the following:

Private Sub CommandButton1_Click()
    Dim idnum As Variant, b As Object, i As Double
    idnum = Right(TextBox1.Value, 3)
[COLOR=#0000ff]    if isnumeric(idnum) then idnum = val(idnum)[/COLOR]
    Set b = Sheet1.Columns("A").Find(idnum, lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        'exists
        i = b.Row
        TextBox1.Text = Sheet1.Cells(i, 1)
        TextBox2.Text = Sheet1.Cells(i, 2)
        TextBox3.Text = Sheet1.Cells(i, 3)
        TextBox4.Text = Sheet1.Cells(i, 4)
        TextBox5.Text = Sheet1.Cells(i, 5)
        TextBox6.Text = Sheet1.Cells(i, 6)
        TextBox7.Text = Sheet1.Cells(i, 7)
        TextBox8.Text = Sheet1.Cells(i, 8)
        TextBox9.Text = Sheet1.Cells(i, 9)
        TextBox10.Text = Sheet1.Cells(i, 10)
        TextBox11.Text = Sheet1.Cells(i, 11)
        TextBox12.Text = Sheet1.Cells(i, 12)
        TextBox13.Text = Sheet1.Cells(i, 13)
        TextBox14.Text = Sheet1.Cells(i, 14)
        TextBox15.Text = Sheet1.Cells(i, 15)
        TextBox16.Text = Sheet1.Cells(i, 16)
    Else
        MsgBox "LHID not found", vbExclamation
    End If
End Sub