Solved

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

Posted on 2008-10-29
1,318 Views
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
``````
0
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
• 4
• 4

LVL 17

Expert Comment

ID: 22829691
could you post an example pls?
0

Author Comment

ID: 22829871
Here you go

Thanks
Example.xls
0

LVL 7

Expert Comment

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
``````
0

Author Comment

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

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
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
``````
0

LVL 7

Expert Comment

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

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

BarryTice earned 500 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

ID: 31511094
0

## Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month7 days, 23 hours left to enroll