Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-29
9
Medium Priority
?
1,324 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
ID: 22829691
could you post an example pls?
0
 

Author Comment

by:quants1
ID: 22829871
Here you go

Thanks
Example.xls
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 22830715
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:quants1
ID: 22831223
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
 
LVL 7

Expert Comment

by:BarryTice
ID: 22831958
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
ID: 22832119
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
ID: 22838294
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 2000 total points
ID: 22840763
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
ID: 31511094
Thank you very much for your help and your clear easy to follow replies
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

927 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