VBA using Loop

[lela0643]

Exercise 12: Find a student
Open the workbook "Résultats examen.xlsx". In the first spreadsheet (Results) you have a list of students.
1) Build a VBA macro that asks the user with a dialog box, the name of the student to find. This macro will browse the list and select the cell corresponding to the name of the student.
2) The macro informs the user, with a second dialog box, if the student has been received.
3) If all the list has been explored and the name has not been found, the macro displays the message “student not found in the list” and select the first empty cell below the names list.

Help please?

 

[Jimmypop]

Hi there...

Try the below...

Sub Search()
'Original Code by dmt32 06/11/2022
'Updated by Jimmypop 02/12/2022
    Dim fnd         As Variant
    Dim FoundCell   As Range
    Dim rng     As Range
    Do
        fnd = InputBox("Enter Student Name To search", "Enter Name")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0

        Set FoundCell = Range("A:A").Find(fnd, , xlValues, xlPart, , , False, , False)
        If Not FoundCell Is Nothing Then
            FoundCell.Select
            MsgBox fnd & Chr(10) & "was found in the list", 48, "Found"
            Exit Sub
        End If

    MsgBox fnd & Chr(10) & "student not found in the list", 48, "Not Found"
    Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.Select
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA using Loop
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[lela0643]

Hi there...

Try the below...

Sub Search()
'Original Code by dmt32 06/11/2022
'Updated by Jimmypop 02/12/2022
    Dim fnd         As Variant
    Dim FoundCell   As Range
    Dim rng     As Range
    Do
        fnd = InputBox("Enter Student Name To search", "Enter Name")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0

        Set FoundCell = Range("A:A").Find(fnd, , xlValues, xlPart, , , False, , False)
        If Not FoundCell Is Nothing Then
            FoundCell.Select
            MsgBox fnd & Chr(10) & "was found in the list", 48, "Found"
            Exit Sub
        End If

    MsgBox fnd & Chr(10) & "student not found in the list", 48, "Not Found"
    Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.Select
End Sub

Thank you, unfortunately after putting the name into the Box it doesn't do anything...

 

[Jimmypop]

Thank you, unfortunately after putting the name into the Box it doesn't do anything...

So after pressing the Ok button nothing happens?

 

[lela0643]

So after pressing the Ok button nothing happens?

yes exactly

 

[Jimmypop]

yes exactly

What is the range to search... Family Name (Range A) or First Name(Range B)?

 

[lela0643]

What is the range to search... Family Name (Range A) or First Name(Range B)?

Range A - Family Name

 

[Jimmypop]

Range A - Family Name

How are you running the macro? Via a button or something else? I tested this and works on my side...

 

[lela0643]

What is the range to search... Family Name (Range A) or First Name(Range B)?

How are you running the macro? Via a button or something else? I tested this and works on my side...

View attachment 80059
View attachment 80060
View attachment 80061

now I was able to make it work, thank you so much!!
How can I make a macro that informs the user, with a second dialog box, if the student has been received?

And another quick question what do the functions StrPtr and Chr do? I am not familiar with those and was wondering wheter they can be replaced by a more simple way?