excel vba function help

[c0259621]

Hi Guys

I have a task at work where i need to create a function based on the data below ( i have attached the sheet to note this is made up example data)

i am struggling with this task and was wondering if anyone can help?

student grade average status
James 55 52% pass
Ian 38 35% fail
Mike 80 74% high pass
Jane 65 67% pass
Will 10 23% none



The function needs to allow a user to type a student name via an input box and then do the following

• If the typed student in the input box isn't in the student list then message should returns 'Student does not exist'
• If the typed student name grade is between 16 and 40 OR the Average is 35 % OR the status is 'fail' then message should return 'undecided'
• if the typed student name grade is between 5 and 15 and % is less or equal to 24% AND status is 'none' then message should return 'Student hasn’t progressed'
• else message should return ' student has progressed'

can anyone help with this please

[6StringJazzer]

This can be done quite easily with only formulas and no VBA. See attached solution.

You said you are struggling but you have attached a file with no VBA. What progress have you made? Why do you require a VBA solution?

[c0259621]

Hi Jeff thank you for this , I have done vba on the original data but i am getting no where with this, they don't want formula as there is a lot of data, they want in interactive as in just type in the students name and get the result thanks

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will do it for you this time: https://www.mrexcel.com/board/thread...lease.1188830/)

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[6StringJazzer]

just type in the students name and get the result

That is exactly what you get if you type a student's name into G3. I just extended it down several rows to show the results for all of your test data, plus a non-existent case, plus a blank case. You can just use one row if you want.

If you want this in VBA instead you will have to be prescriptive about what you want this to look like to the user.

[c0259621]

That is exactly what you get if you type a student's name into G3. I just extended it down several rows to show the results for all of your test data, plus a non-existent case, plus a blank case. You can just use one row if you want.

If you want this in VBA instead you will have to be prescriptive about what you want this to look like to the user.

Thank you, when the function is run i need an input box to appear stating "type in students name and press ok" when that is done the the function runs based on the rules i stated before. thank you for your help

[6StringJazzer]

What makes the function run?

[c0259621]

What makes the function run?

HI sorry for not stating this and being unclear ( i appreciate your help), a macro button is run that brings up the InputBox which always you to type the name of the students and then when you press ok this runs the function.

sorry hope i am making sense

[bebo021999]

Assign this macro into a button:

Sub test() 
Dim Stud As String 
Stud = InputBox("Studen name:", "Student status") 
Lr = Cells(Rows.Count, "A").End(xlUp).Row 
For i = 4 To Lr 
    If Range("A4:A" & Lr).Find(Stud) Is Nothing Then 
    MsgBox "Students does not exist", vbOKCancel 
    Exit For 
    Else 
    If Cells(i, "A") = Stud Then 
        If Cells(i, "B") >= 16 And Cells(i, "B") <= 40 Or Cells(i, "C") = 35 Or Cells(i, "D") = "fail" Then 
        MsgBox "undecided", vbOKCancel 
        Exit For 
        Else 
            If Cells(i, "B") >= 5 And Cells(i, "B") <= 15 And Cells(i, "C") <= 24 And Cells(i, "D") = "none" Then 
            MsgBox "Student hasn't progressed", vbOKCancel 
            Exit For 
            Else 
            MsgBox "Student has progressed", vbOKCancel 
            Exit For 
            End If 
        End If 
     End If 
  End If 
Next 
End Sub  

[6StringJazzer]

A VBA solution has also been posted at the cross-post linked by Ali, so there is duplicate effort. This illustrates why this rule exists (in both forums).