Solved

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

Posted on 2008-10-29
9
1,312 Views
Last Modified: 2012-05-05
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
Comment
Question by:quants1
  • 4
  • 4
9 Comments
 
LVL 17

Expert Comment

by:ExcelGuide
Comment Utility
could you post an example pls?
0
 

Author Comment

by:quants1
Comment Utility
Here you go

Thanks
Example.xls
0
 
LVL 7

Expert Comment

by:BarryTice
Comment Utility
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
 

Author Comment

by:quants1
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Expert Comment

by:BarryTice
Comment Utility
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
 
LVL 7

Expert Comment

by:BarryTice
Comment Utility
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
 

Author Comment

by:quants1
Comment Utility
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
 
LVL 7

Accepted Solution

by:
BarryTice earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:quants1
Comment Utility
Thank you very much for your help and your clear easy to follow replies
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now