excel vba help please

[Imran Azam]

Hi Guys

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

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

please note this is in sheet 1 from A4-d9

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'

 

[Fluff]

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: excel vba function help
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.

 

[Imran Azam]

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: excel vba function help
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.

hi how do i provide this link?

 

[Fluff]

You don't need to this time, as I have done it for you. But you need to do it yourself if you cross post in the future.

 

[Imran Azam]

You don't need to this time, as I have done it for you. But you need to do it yourself if you cross post in the future.

thank you

 

[6StringJazzer]

Non-VBA solution posted in cross-post thread

c0259621=TESTsheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2							Student Name	Grade	Average	Result
3	student	grade	average %	status			James	55	52	Student has progressed
4	James	55	52	pass
5	Ian	38	35	fail
6	Mike	80	74	high pass
7	Jane	65	67	pass
8	Will	10	23	none
Sheet1

Cell Formulas
Range	Formula
H3	H3	=IFERROR(IF(G3="","",VLOOKUP(G3,A:B,2,FALSE)),"")
I3	I3	=IFERROR(VLOOKUP(G3,A:C,3,FALSE),"")
J3	J3	=IF(G3="","",IF(ISERROR(MATCH(G3,A:A,0)),"Student does not exist",IF(OR(AND(H3>=16,H3<=40),C4=35,D4="fail"),"Undecided",IF(AND(H3>=5,H3<=15,C4<=24,D4="none"),"Student hasn't progressed","Student has progressed"))))

 

[Imran Azam]

Non-VBA solution posted in cross-post thread

c0259621=TESTsheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2							Student Name	Grade	Average	Result
3	student	grade	average %	status			James	55	52	Student has progressed
4	James	55	52	pass
5	Ian	38	35	fail
6	Mike	80	74	high pass
7	Jane	65	67	pass
8	Will	10	23	none
Sheet1

Cell Formulas
Range	Formula
H3	H3	=IFERROR(IF(G3="","",VLOOKUP(G3,A:B,2,FALSE)),"")
I3	I3	=IFERROR(VLOOKUP(G3,A:C,3,FALSE),"")
J3	J3	=IF(G3="","",IF(ISERROR(MATCH(G3,A:A,0)),"Student does not exist",IF(OR(AND(H3>=16,H3<=40),C4=35,D4="fail"),"Undecided",IF(AND(H3>=5,H3<=15,C4<=24,D4="none"),"Student hasn't progressed","Student has progressed"))))

thanks for this but due to the requirements needs to be a vba soluation

 

[Alex Blakenburg]

thanks for this but due to the requirements needs to be a vba soluation

This question is almost identical to the one you asked in a previous thread.
All you need to do is slightly modify the answer provided by @offthelip there
function help

Here is my modification of that code.

Sub StudentStatus()


'    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'


    Dim inStudentName As String
    Dim msgStatus As String
    Dim lastrow As Long, i As Long
    Dim notfnd As Boolean
    Dim inarr As Variant
    
    inStudentName = InputBox("Enter student's name: ")
    If inStudentName = "" Then Exit Sub
    
    
    With Worksheets("sheet1")
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        inarr = .Range(.Cells(4, 1), .Cells(lastrow, 4))
    End With
    notfnd = True
        For i = 1 To UBound(inarr, 1)
          If LCase(inarr(i, 1)) = inStudentName Then
            notfnd = False
              msgStatus = "Student " & inStudentName & " is normal"
            If (inarr(i, 2) < 40 And inarr(i, 2) > 16) Or inarr(i, 3) = 35 Or LCase(inarr(i, 4)) = "fail" Then
                msgStatus = "Student " & inStudentName & " undecided"
            ElseIf (inarr(i, 2) < 15 And inarr(i, 2) > 5) And inarr(i, 3) <= 24 And LCase(inarr(i, 4)) = "none" Then
                msgStatus = "Student " & inStudentName & " Student hasn’t progressed"
            Else
                msgStatus = "Student " & inStudentName & " Student has progressed"
            End If
            Exit For
          End If
        Next i
        If notfnd Then
              msgStatus = "Student does not exist"
        End If
        
        MsgBox msgStatus
End Sub

 

[Imran Azam]

This question is almost identical to the one you asked in a previous thread.
All you need to do is slightly modify the answer provided by @offthelip there
function help

Here is my modification of that code.

Sub StudentStatus()


'    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'


    Dim inStudentName As String
    Dim msgStatus As String
    Dim lastrow As Long, i As Long
    Dim notfnd As Boolean
    Dim inarr As Variant
   
    inStudentName = InputBox("Enter student's name: ")
    If inStudentName = "" Then Exit Sub
   
   
    With Worksheets("sheet1")
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        inarr = .Range(.Cells(4, 1), .Cells(lastrow, 4))
    End With
    notfnd = True
        For i = 1 To UBound(inarr, 1)
          If LCase(inarr(i, 1)) = inStudentName Then
            notfnd = False
              msgStatus = "Student " & inStudentName & " is normal"
            If (inarr(i, 2) < 40 And inarr(i, 2) > 16) Or inarr(i, 3) = 35 Or LCase(inarr(i, 4)) = "fail" Then
                msgStatus = "Student " & inStudentName & " undecided"
            ElseIf (inarr(i, 2) < 15 And inarr(i, 2) > 5) And inarr(i, 3) <= 24 And LCase(inarr(i, 4)) = "none" Then
                msgStatus = "Student " & inStudentName & " Student hasn’t progressed"
            Else
                msgStatus = "Student " & inStudentName & " Student has progressed"
            End If
            Exit For
          End If
        Next i
        If notfnd Then
              msgStatus = "Student does not exist"
        End If
       
        MsgBox msgStatus
End Sub

thank you so much

 

[6StringJazzer]

A VBA solution has also been posted in the cross-post linked in post #4. An unfortunate duplication of effort.