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

quants1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
quants1Author Commented:
Thank you very much for your help and your clear easy to follow replies
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.