• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1328
  • Last Modified:

Mircosoft, Excel, 2007, Visual Basic, Using multiple if then else commands

I have two worksheets
Woksheet 1 has name and class plus an empty columns for each subject to have a grade put into
Worksheet 2 has name and subject plus a grade

I need to to compare the name columns and then link the class to a subject and then pull over the grade.and put in a zero if the student does not study that subject.

I have tried many combinations of the if then and else commands but no joy at the moment I just get zeros and blanks no grade

I would appreciate any help thanks in advance


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

Open in new window

0
quants1
Asked:
quants1
  • 4
  • 4
1 Solution
 
ExcelGuideConsultantCommented:
could you post an example pls?
0
 
quants1Author Commented:
Here you go

Thanks
Example.xls
0
 
BarryTiceCommented:
It looks to me like you have all your end if statements in the wrong places.

As it stands, if Cells(n,2).Value is not "English" then none of the other conditionals ever get tested.

This is why it's a good thing to indent your code, by the way :-)
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

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
quants1Author Commented:
Thanks for the try but now I am getting zeros where there should be grades

            Engllish      Maths      Science                  
Student 1      English      0            
Student 1      Maths            0      
Student 1      Science                  0            
Student 2      English      0            
Student 2      Maths            0      
Student 2      Science                  0
Student 3      English      0            
Student 3      Maths            0      
Student 3      Science                  

0
 
BarryTiceCommented:
Sometimes, the best thing is to reassess what you're trying to do and start from scratch.

I THINK this is what you're looking for. Note, though, that you have two Science columns with no way to distinguish them, so I've put the same grade into both of them.
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

Open in new window

0
 
BarryTiceCommented:
In retrospect, you should change lines 9 and 15 in that previous code sample to start on row 3:

For intRow = 3 To 1270

    For intFFRow = 3 To 1025
0
 
quants1Author Commented:
Thank you this works well for my example however some of the later courses do not have the same course name on sheet one as the subject name on fftmost likely.

i.e 11A/Hb is going to match iwth Hu'ties

I thought about changing the 11A/hb to Hu'ties but I can because 11A/hb will need column AB

where as 11A/bt could aslo match to Hu'ties but needs column Y
0
 
BarryTiceCommented:
If that's the case, then what is line 20 in my previous post would have to be adjusted somewhat.

Declare strFFTSubject as a String at the beginning of the code, and then replace
If strSubject = Worksheets("fftmostlikely").Range("L" & strFFRow).Text Then

with something like:

strFFTSubject = Worksheets("fftmostlikely").Range("L" & strFFRow).Text
If strSubject = strFFTSubject Or (strSubject = "11A/Hb" And strFFTSubject = "Hu'ties") Then

You may need to expand that list of "Or" options depending on how many others don't match.
0
 
quants1Author Commented:
Thank you very much for your help and your clear easy to follow replies
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now