Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-29
9
Medium Priority
?
1,321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

715 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