Total point grading system review!

L

LONGO

New Member
Joined
Sep 14, 2017
Messages
12
Hello!
Im confusing how to join two formula in excel at once as what Mr AlanY did like this so PLEASE! I need an help.
Basically Im grading the student who is taking combination of three subjects, with TWO criteria

  1. By considering weight of points as in table below shows;
  2. By DIVISION without considering the aggregated point should have at least two subsidiaries (S) or at least One Principal Pass (E), otherwise if has less than two S will get O<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]-->
As what table show below:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PNT[/TD]
[TD]DIV[/TD]
[TD]SU[/TD]
[TD]BJE[/TD]
[TD]CT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD]C[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]POINT[/TD]
[TD]DIVISION[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]90
[/TD]
[TD="align: center"]80
[/TD]
[TD="align: center"]90
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]III
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"]88
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]II
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]55
[/TD]
[TD="align: center"]67
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]87
[/TD]
[TD="align: center"]76
[/TD]
[TD="align: center"]77
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]ABS
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]16
[/TD]
[TD="align: center"]III
[/TD]
[TD="align: center"]54
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]IV
[/TD]
[TD="align: center"]45
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]IV
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]X
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]IV
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]X
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: center"]-
[/TD]
[TD="align: center"]IV
[/TD]
[TD="align: center"]70
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]X
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Column A&B is what is needed to appear at column I & J
The guide is:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]GRADE[/TD]
[TD]SCORE RANGE[/TD]
[TD]GRADE POINT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]80-100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]70-79[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]60-69[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]50-59[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]40-49[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]35-39[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]0-34[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in Advance,
Im ready to hear more from you!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You have given a table to calculate PNT in column A. Fine.
If you tell how you calculated manually DIV in column B, it will help to come up with a solution.
 
Last edited:
Upvote 0
pmich said:
You have given a table to calculate PNT in column A. Fine.
If you tell how you calculated manually DIV in column B, it will help to come up with a solution.
Click to expand...

Hello!
In POINTS i used {=IFERROR(SUM(LARGE(K14:M14,ROW(INDIRECT("$1:$"&MIN(3,COUNT(K14:M14)))))),"-")}

and DIVISION i used {=IFERROR(LOOKUP(F14,{3;10;13;18;20},{"I","II","III","IV",0}),"ABS")}
But i need to appear as in column A and B what i did manually.
 
Last edited:
Upvote 0
I am not good at using long formulas. If you can tell me what you do with your formula to get DIV, then I may come up with VBA Code. If you are particular about formula, hope some expert will help you.
 
Upvote 0
pmich said:
I am not good at using long formulas. If you can tell me what you do with your formula to get DIV, then I may come up with VBA Code. If you are particular about formula, hope some expert will help you.
Click to expand...
Hell!
In order to get DIV i use the following guide
First point 3-9 =DIV I
10-12= DIV II
13-17=DIV III
18-19=DIV IV; at least two S or one E wil get DIV IV without point (-)
20-21=DIV O ; less than two S gets DIV O
 
Upvote 0
Thanks for the Table for the Guide to calculate DIV.
Score in C10 is F. Score in D10 is F.
At least two S or one E will get DIV IV.
F10 + G10 = 14 points.
But, when you have 2 F and only 14 points, how did you get IV in B10?
Also explain how you got IV in B11 & B12?
What do you mean by 'without point (-)'?
 
Upvote 0
Agrrr!! Im sorry, B11 & B12 ITS DIV O i wrote wrong that cells

Hello
I meant if you have S+S+X or S+S+F= IV
E+X+X or E+F+X or E+F+F=IV
S+X+X= or S+F+X or S+F+F= O
It that columns in table i did wrong, ( - ) means it should replace point in DIV IV or O where the condition above is used.
Thanks for your responses.
 
Upvote 0
<code>
Code: 
Private Sub cmdPrepareGrades_Click()
 'https://www.mrexcel.com/forum/excel-questions/1041937-total-point-grading-system-review.html
 Sheets("Grades").Select
 Range("c3").Select
 Dim varSrchNum As Long
 Dim PntVal As Integer
 varSrchNum = 3
 Range("c" & Trim(Str(varSrchNum))).Select
 Do Until Selection.Value = ""    
    If Selection.Value > -1 And Selection.Value < 35 Then
     Selection.Offset(0, 3).Value = 7
     Selection.Offset(0, 8).Value = "F"
    ElseIf Selection.Value > 34 And Selection.Value < 40 Then
     Selection.Offset(0, 3).Value = 6
     Selection.Offset(0, 8).Value = "S"
    ElseIf Selection.Value > 39 And Selection.Value < 50 Then
     Selection.Offset(0, 3).Value = 5
     Selection.Offset(0, 8).Value = "E"
    ElseIf Selection.Value > 49 And Selection.Value < 60 Then
     Selection.Offset(0, 3).Value = 4
     Selection.Offset(0, 8).Value = "D"
    ElseIf Selection.Value > 59 And Selection.Value < 70 Then
     Selection.Offset(0, 3).Value = 3
     Selection.Offset(0, 8).Value = "C"
    ElseIf Selection.Value > 69 And Selection.Value < 80 Then
     Selection.Offset(0, 3).Value = 2
     Selection.Offset(0, 8).Value = "B"
    ElseIf Selection.Value > 79 And Selection.Value < 101 Then
     Selection.Offset(0, 3).Value = 1
     Selection.Offset(0, 8).Value = "A"
    End If
    
    If Selection.Value = "X" Then
     Selection.Offset(0, 3).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 8).Value = "X"
     PntVal = 0
    Else
      PntVal = Val(Selection.Offset(0, 3))
    End If
    
    If Selection.Offset(0, 1).Value > -1 And Selection.Offset(0, 1).Value < 35 Then
     Selection.Offset(0, 4).Value = 7
     Selection.Offset(0, 9).Value = "F"
    ElseIf Selection.Offset(0, 1).Value > 34 And Selection.Offset(0, 1).Value < 40 Then
     Selection.Offset(0, 4).Value = 6
     Selection.Offset(0, 9).Value = "S"
    ElseIf Selection.Offset(0, 1).Value > 39 And Selection.Offset(0, 1).Value < 50 Then
     Selection.Offset(0, 4).Value = 5
     Selection.Offset(0, 9).Value = "E"
    ElseIf Selection.Offset(0, 1).Value > 49 And Selection.Offset(0, 1).Value < 60 Then
     Selection.Offset(0, 4).Value = 4
     Selection.Offset(0, 9).Value = "D"
    ElseIf Selection.Offset(0, 1).Value > 59 And Selection.Offset(0, 1).Value < 70 Then
     Selection.Offset(0, 4).Value = 3
     Selection.Offset(0, 9).Value = "C"
    ElseIf Selection.Offset(0, 1).Value > 69 And Selection.Offset(0, 1).Value < 80 Then
     Selection.Offset(0, 4).Value = 2
     Selection.Offset(0, 9).Value = "B"
    ElseIf Selection.Offset(0, 1).Value > 79 And Selection.Offset(0, 1).Value < 101 Then
     Selection.Offset(0, 4).Value = 1
     Selection.Offset(0, 9).Value = "A"
    End If
    
    If Selection.Offset(0, 1).Value = "X" Then
     Selection.Offset(0, 4).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 9).Value = "X"
     PntVal = 0
    Else
     If PntVal > 0 Then
      PntVal = PntVal + Val(Selection.Offset(0, 4))
     End If
    End If
    
    If Selection.Offset(0, 2).Value > -1 And Selection.Offset(0, 2).Value < 35 Then
     Selection.Offset(0, 5).Value = 7
     Selection.Offset(0, 10).Value = "F"
    ElseIf Selection.Offset(0, 2).Value > 34 And Selection.Offset(0, 2).Value < 40 Then
     Selection.Offset(0, 5).Value = 6
     Selection.Offset(0, 10).Value = "S"
    ElseIf Selection.Offset(0, 2).Value > 39 And Selection.Offset(0, 2).Value < 50 Then
     Selection.Offset(0, 5).Value = 5
     Selection.Offset(0, 10).Value = "E"
    ElseIf Selection.Offset(0, 2).Value > 49 And Selection.Offset(0, 2).Value < 60 Then
     Selection.Offset(0, 5).Value = 4
     Selection.Offset(0, 10).Value = "D"
    ElseIf Selection.Offset(0, 2).Value > 59 And Selection.Offset(0, 2).Value < 70 Then
     Selection.Offset(0, 5).Value = 3
     Selection.Offset(0, 10).Value = "C"
    ElseIf Selection.Offset(0, 2).Value > 69 And Selection.Offset(0, 2).Value < 80 Then
     Selection.Offset(0, 5).Value = 2
     Selection.Offset(0, 10).Value = "B"
    ElseIf Selection.Offset(0, 2).Value > 79 And Selection.Offset(0, 2).Value < 101 Then
     Selection.Offset(0, 5).Value = 1
     Selection.Offset(0, 10).Value = "A"
    End If
    
    If Selection.Offset(0, 2).Value = "X" Then
     Selection.Offset(0, 5).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 10).Value = "X"
     PntVal = 0
    Else
     If PntVal > 0 Then
      PntVal = PntVal + Val(Selection.Offset(0, 5))
     End If
    End If
    
    If PntVal > 2 And PntVal < 10 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "I"
    ElseIf PntVal > 9 And PntVal < 13 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "II"
    ElseIf PntVal > 12 And PntVal < 18 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "III"
    ElseIf PntVal > 17 And PntVal < 20 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "IV"
    ElseIf PntVal > 19 And PntVal < 22 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "O"
    End If
    
   If Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "S" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "O"
    End If
    
   End If
    
   If Selection.Value = "X" And Selection.Offset(0, 1).Value = "X" And Selection.Offset(0, 2).Value = "X" Then
    Selection.Offset(0, 3).Value = "X"
    Selection.Offset(0, 4).Value = "X"
    Selection.Offset(0, 5).Value = "X"
    Selection.Offset(0, 6).Value = "-"
    Selection.Offset(0, 7).Value = "ABS"
   End If
      varSrchNum = varSrchNum + 1
   Range("c" & Trim(Str(varSrchNum))).Select
 Loop
End Sub
</code>
You have not given the DIV for FXX and BXX.
 
Last edited:
Upvote 0
pmich said:
<code>
Code: 
Private Sub cmdPrepareGrades_Click()
 'https://www.mrexcel.com/forum/excel-questions/1041937-total-point-grading-system-review.html
 Sheets("Grades").Select
 Range("c3").Select
 Dim varSrchNum As Long
 Dim PntVal As Integer
 varSrchNum = 3
 Range("c" & Trim(Str(varSrchNum))).Select
 Do Until Selection.Value = ""    
    If Selection.Value > -1 And Selection.Value < 35 Then
     Selection.Offset(0, 3).Value = 7
     Selection.Offset(0, 8).Value = "F"
    ElseIf Selection.Value > 34 And Selection.Value < 40 Then
     Selection.Offset(0, 3).Value = 6
     Selection.Offset(0, 8).Value = "S"
    ElseIf Selection.Value > 39 And Selection.Value < 50 Then
     Selection.Offset(0, 3).Value = 5
     Selection.Offset(0, 8).Value = "E"
    ElseIf Selection.Value > 49 And Selection.Value < 60 Then
     Selection.Offset(0, 3).Value = 4
     Selection.Offset(0, 8).Value = "D"
    ElseIf Selection.Value > 59 And Selection.Value < 70 Then
     Selection.Offset(0, 3).Value = 3
     Selection.Offset(0, 8).Value = "C"
    ElseIf Selection.Value > 69 And Selection.Value < 80 Then
     Selection.Offset(0, 3).Value = 2
     Selection.Offset(0, 8).Value = "B"
    ElseIf Selection.Value > 79 And Selection.Value < 101 Then
     Selection.Offset(0, 3).Value = 1
     Selection.Offset(0, 8).Value = "A"
    End If
    
    If Selection.Value = "X" Then
     Selection.Offset(0, 3).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 8).Value = "X"
     PntVal = 0
    Else
      PntVal = Val(Selection.Offset(0, 3))
    End If
    
    If Selection.Offset(0, 1).Value > -1 And Selection.Offset(0, 1).Value < 35 Then
     Selection.Offset(0, 4).Value = 7
     Selection.Offset(0, 9).Value = "F"
    ElseIf Selection.Offset(0, 1).Value > 34 And Selection.Offset(0, 1).Value < 40 Then
     Selection.Offset(0, 4).Value = 6
     Selection.Offset(0, 9).Value = "S"
    ElseIf Selection.Offset(0, 1).Value > 39 And Selection.Offset(0, 1).Value < 50 Then
     Selection.Offset(0, 4).Value = 5
     Selection.Offset(0, 9).Value = "E"
    ElseIf Selection.Offset(0, 1).Value > 49 And Selection.Offset(0, 1).Value < 60 Then
     Selection.Offset(0, 4).Value = 4
     Selection.Offset(0, 9).Value = "D"
    ElseIf Selection.Offset(0, 1).Value > 59 And Selection.Offset(0, 1).Value < 70 Then
     Selection.Offset(0, 4).Value = 3
     Selection.Offset(0, 9).Value = "C"
    ElseIf Selection.Offset(0, 1).Value > 69 And Selection.Offset(0, 1).Value < 80 Then
     Selection.Offset(0, 4).Value = 2
     Selection.Offset(0, 9).Value = "B"
    ElseIf Selection.Offset(0, 1).Value > 79 And Selection.Offset(0, 1).Value < 101 Then
     Selection.Offset(0, 4).Value = 1
     Selection.Offset(0, 9).Value = "A"
    End If
    
    If Selection.Offset(0, 1).Value = "X" Then
     Selection.Offset(0, 4).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 9).Value = "X"
     PntVal = 0
    Else
     If PntVal > 0 Then
      PntVal = PntVal + Val(Selection.Offset(0, 4))
     End If
    End If
    
    If Selection.Offset(0, 2).Value > -1 And Selection.Offset(0, 2).Value < 35 Then
     Selection.Offset(0, 5).Value = 7
     Selection.Offset(0, 10).Value = "F"
    ElseIf Selection.Offset(0, 2).Value > 34 And Selection.Offset(0, 2).Value < 40 Then
     Selection.Offset(0, 5).Value = 6
     Selection.Offset(0, 10).Value = "S"
    ElseIf Selection.Offset(0, 2).Value > 39 And Selection.Offset(0, 2).Value < 50 Then
     Selection.Offset(0, 5).Value = 5
     Selection.Offset(0, 10).Value = "E"
    ElseIf Selection.Offset(0, 2).Value > 49 And Selection.Offset(0, 2).Value < 60 Then
     Selection.Offset(0, 5).Value = 4
     Selection.Offset(0, 10).Value = "D"
    ElseIf Selection.Offset(0, 2).Value > 59 And Selection.Offset(0, 2).Value < 70 Then
     Selection.Offset(0, 5).Value = 3
     Selection.Offset(0, 10).Value = "C"
    ElseIf Selection.Offset(0, 2).Value > 69 And Selection.Offset(0, 2).Value < 80 Then
     Selection.Offset(0, 5).Value = 2
     Selection.Offset(0, 10).Value = "B"
    ElseIf Selection.Offset(0, 2).Value > 79 And Selection.Offset(0, 2).Value < 101 Then
     Selection.Offset(0, 5).Value = 1
     Selection.Offset(0, 10).Value = "A"
    End If
    
    If Selection.Offset(0, 2).Value = "X" Then
     Selection.Offset(0, 5).Value = "X"
     Selection.Offset(0, 6).Value = "-"
     Selection.Offset(0, 10).Value = "X"
     PntVal = 0
    Else
     If PntVal > 0 Then
      PntVal = PntVal + Val(Selection.Offset(0, 5))
     End If
    End If
    
    If PntVal > 2 And PntVal < 10 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "I"
    ElseIf PntVal > 9 And PntVal < 13 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "II"
    ElseIf PntVal > 12 And PntVal < 18 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "III"
    ElseIf PntVal > 17 And PntVal < 20 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "IV"
    ElseIf PntVal > 19 And PntVal < 22 Then
     Selection.Offset(0, 6).Value = PntVal
     Selection.Offset(0, 7).Value = "O"
    End If
    
   If Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "S" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "O"
    End If
    
   End If
    
   If Selection.Value = "X" And Selection.Offset(0, 1).Value = "X" And Selection.Offset(0, 2).Value = "X" Then
    Selection.Offset(0, 3).Value = "X"
    Selection.Offset(0, 4).Value = "X"
    Selection.Offset(0, 5).Value = "X"
    Selection.Offset(0, 6).Value = "-"
    Selection.Offset(0, 7).Value = "ABS"
   End If
      varSrchNum = varSrchNum + 1
   Range("c" & Trim(Str(varSrchNum))).Select
 Loop
End Sub
</code>
You have not given the DIV for FXX and BXX.
Click to expand...

Hello!
Thanks for the Code
FXX and BXX its DIV IV without point like in point cell should appear "-".
 
Upvote 0
Rich (BB code): 
If Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "S" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "E" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    Selection.Offset(0, 7).Value = "IV"
   ElseIf Selection.Offset(0, 8).Value = "S" And Selection.Offset(0, 9).Value = "F" Then
    If Selection.Offset(0, 10).Value = "X" Or Selection.Offset(0, 10).Value = "F" Then
     Selection.Offset(0, 7).Value = "O"
    End If
   ElseIf Selection.Offset(0, 9).Value = "X" And Selection.Offset(0, 10).Value = "X" Then
    If Selection.Offset(0, 8).Value = "F" Or Selection.Offset(0, 8).Value = "B" Then
     Selection.Offset(0, 7).Value = "IV"
    End If
End If
Include the code in RED.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Auto filling a cell in a table which has a list using data validation
2
Replies
14
Views
163
rjn008
R
I
  • Solved
Also have the row number shown in Listbox on userform advice
Replies
7
Views
76
NoSparks
N
GMC The Macro Man
  • Solved
Count Not of Grades
Replies
3
Views
171
GMC The Macro Man
GMC The Macro Man
A
  • Solved
why the code is slow despite of using advanced filter with simple data
2 3
Replies
23
Views
414
abdelfattah
A
F
  • Solved
Put a mark on rows that have the same 10 digits but different last one digit
Replies
2
Views
187
feni1388
F

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top