Dim i As Integer
Dim n As Integer
For n = 1 To 1270
code = Cells(n, 1)
Set rng = Worksheets("fftmostlikely").Range("a1:ae1025")
For i = 1 To rng.Count
If rng.Cells(i).Value = code Then
If Cells(n, 2).Value = "English" Then
If rng.Cells(n, 12).Value = "English" Then Cells(n, 3) = rng.Cells(i).Offset(0, 22).Value
Else: Cells(n, 3) = "0"
If Cells(n, 2).Value = "Maths" Then
If rng.Cells(n, 12).Value = "Maths" Then Cells(n, 4) = rng.Cells(i).Offset(0, 22).Value
Else: Cells(n, 4) = "0"
If Cells(n, 2) = "Science" Then
If rng.Cells(n, 12).Value = "Science" Then Cells(n, 5) = rng.Cells(i).Offset(0, 22).Value
Else: Cells(n, 5) = "0"
ElseIf Cells(n, 2) = "Science" Then
If rng.Cells(n, 12).Value = "Science" Then Cells(n, 6) = rng.Cells(i).Offset(0, 22).Value
Else: Cells(n, 6) = "0"
End If
End If
End If
End If
End If
Next i
Next n
End Sub
Dim i As Integer
Dim n As Integer
For n = 1 To 1270
code = Cells(n, 1)
Set rng = Worksheets("fftmostlikely").Range("a1:ae1025")
For i = 1 To rng.Count
If rng.Cells(i).Value = code Then
If Cells(n, 2).Value = "English" Then
If rng.Cells(n, 12).Value = "English" Then
Cells(n, 3) = rng.Cells(i).Offset(0, 22).Value
Else
Cells(n, 3) = "0"
End If
ElseIf Cells(n, 2).Value = "Maths" Then
If rng.Cells(n, 12).Value = "Maths" Then
Cells(n, 4) = rng.Cells(i).Offset(0, 22).Value
Else
Cells(n, 4) = "0"
End If
ElseIf Cells(n, 2) = "Science" Then
If rng.Cells(n, 12).Value = "Science" Then
Cells(n, 5) = rng.Cells(i).Offset(0, 22).Value
Else
Cells(n, 5) = "0"
End If
ElseIf Cells(n, 2) = "Science" Then
If rng.Cells(n, 12).Value = "Science" Then
Cells(n, 6) = rng.Cells(i).Offset(0, 22).Value
Else
Cells(n, 6) = "0"
End If
End If
End If
Next i
Next n
End Sub
Private Sub CommandButton1_Click()
Dim intRow As Integer
Dim strRow As String
Dim intFFRow As Integer
Dim strFFRow As String
Dim strStudent As String
Dim strSubject As String
For intRow = 1 To 1270
strRow = Trim(Str(intRow))
strStudent = Range("Sheet1!A" & strRow).Text
' Set all subjects for this student to 0
Range("Sheet1!C" & strRow & ":F" & strRow).Formula = 0
' Now go harvest grades
For intFFRow = 1 To 1025
strFFRow = Trim(Str(intFFRow))
If Worksheets("fftmostlikely").Range("A" & strFFRow).Text = strStudent Then
' We have the right student.
strSubject = Worksheets("Sheet1").Range("B" & strRow).Text
If strSubject = Worksheets("fftmostlikely").Range("L" & strFFRow).Text Then
' We have the matching subject.
If strSubject = "English" Then
Worksheets("Sheet1").Range("C" & strRow).Formula = Worksheets("fftmostlikely").Range("W" & strFFRow).Text
ElseIf strSubject = "Maths" Then
Worksheets("Sheet1").Range("D" & strRow).Formula = Worksheets("fftmostlikely").Range("W" & strFFRow).Text
ElseIf strSubject = "Science" Then
Worksheets("Sheet1").Range("E" & strRow).Formula = Worksheets("fftmostlikely").Range("W" & strFFRow).Text
Worksheets("Sheet1").Range("F" & strRow).Formula = Worksheets("fftmostlikely").Range("W" & strFFRow).Text
End If
End If
End If
Next intFFRow
Next intRow
End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!