Why is this ElseIF not the same as this Select Case?

[Saladsamurai]

I am trying to clean up this code. But when I run it, the data that it produces changes slightly.

This produces the CORRECT Data:
[vba]Sub CI()
For i = 1 To nRow
For j = 1 To nCol
' Check # times CFD predicts Green and ISX predicts Green, Yellow, or Red

If BestCI_CFD.Cells(i, j) >= 0.9 And BestCI_CFD.Cells(i, j) <> "" And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then

If BestCI_ISX.Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xgr = xgr + 1

End If

G = G + 1

' Check # times CFD predicts Yellow and ISX predicts Green, Yellow, or Red
ElseIf BestCI_CFD.Cells(i, j) < 9 And BestCI_CFD.Cells(i, j) > 0.8 And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then
If BestCI_ISX.Cells(i, j) >= 0.9 Then
xyg = xyg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xyy = xyy + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xyr = xyr + 1
End If
Y = Y + 1
' Check # times CFD predicts Red and ISX predicts Green, Yellow, or Red
ElseIf BestCI_CFD.Cells(i, j) <= 0.8 And BestCI_CFD.Cells(i, j) <> "" And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then
If BestCI_ISX.Cells(i, j) >= 0.9 Then
xrg = xrg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xry = xry + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xrr = xrr + 1
End If
R = R + 1


End If

Next j
Next i

TotalRacks = G + Y + R
End Sub
[/vba]

But this does not. It appears that the value of xgg increases in this one. I will try to narrow it down a little more.

[vba]Sub CI()
With Worksheets("Best CI ISX")
For i = 1 To nRow
For j = 1 To nCol

If Worksheets("Best CI CFD").Cells(i, j) >= 0.9 And _
Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xgg = xgg + 1

Case Is > 0.8
xgy = xgy + 1

Case Is <= 0.8
xgr = xgr + 1

End Select
G = G + 1

ElseIf Worksheets("Best CI CFD").Cells(i, j) < 9 _
And Worksheets("Best CI CFD").Cells(i, j) > 0.8 _
And Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xyg = xyg + 1

Case Is > 0.8
xyy = xyy + 1

Case Is <= 0.8
xyr = xyr + 1

End Select
Y = Y + 1

ElseIf Worksheets("Best CI CFD").Cells(i, j) < 0.8 _
And Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xrg = xrg + 1

Case Is > 0.8
xry = xry + 1

Case Is <= 0.8
xrr = xrr + 1

End Select
R = R + 1

End If
Next j
Next i
End With


TotalRacks = G + Y + R
End SUb[/vba]

[Bob Phillips]

They worked the same for me. What data wors differently?

[Saladsamurai]

I thought that xgg must have changed based on the output.

I really didn't want to upload the whole book since it would be too much to ask for someone to look through it for something as superficial as making the code look 'cleaner.'

I am currently breaking the code up into very small modules to clean it and make it super easy to follow.

Perhaps I will post back to this once I have completed that. Maybe I can offer some more insight as to what is changing.

The biggest change was the value that is given by the ratio of

xgg / G

It increased from 87% to 93 % using the exact same spreadsheet data.

EDIT: I also found a nasty error. Fortunately, it did not affect the results at all.

Line [VBA]ElseIf BestCI_CFD.Cells(i, j) < 9 And BestCI_CFD.Cells(i, j) > 0.8 And BestCI_CFD.Cells ...etc[/VBA]

Should be 0.9 not 9 ...same thing in the select case.

[p45cal]

If TotalRacks comes out different, which is the sum of G,Y and R, the Select Case aspect of the code doesn't impact on this at all. So the only difference is in the three outer Else If conditions, and the first thing I'd look at is to check whether:

BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c"

really is the equivalent of:

Worksheets("Best CI CFD").Cells(i, j) <> ""

by looking at the sheet and seeing if there are any other values in there, especially white space.



Also in the last two outer elseifs of the second sub, there is nothing to cater for a value of exactly 0.8, whereas it is catered for in the first sub.

I've had a go at highlighting these in red and purple below.


[vba]Sub CI()
For i = 1 To nRow
For j = 1 To nCol
' Check # times CFD predicts Green and ISX predicts Green, Yellow, or Red

If BestCI_CFD.Cells(i, j) >= 0.9 And BestCI_CFD.Cells(i, j) <> "" And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then

If BestCI_ISX.Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xgr = xgr + 1

End If

G = G + 1

' Check # times CFD predicts Yellow and ISX predicts Green, Yellow, or Red
ElseIf BestCI_CFD.Cells(i, j) < 9 And BestCI_CFD.Cells(i, j) > 0.8 And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then
If BestCI_ISX.Cells(i, j) >= 0.9Then
xyg = xyg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xyy = xyy + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xyr = xyr + 1
End If
Y = Y + 1
' Check # times CFD predicts Red and ISX predicts Green, Yellow, or Red
ElseIf BestCI_CFD.Cells(i, j) <= 0.8 And BestCI_CFD.Cells(i, j) <> "" And BestCI_CFD.Cells(i, j) <> "$" And BestCI_CFD.Cells(i, j) <> "c" Then
If BestCI_ISX.Cells(i, j) >= 0.9 Then
xrg = xrg + 1
ElseIf BestCI_ISX.Cells(i, j) < 0.9 And BestCI_ISX.Cells(i, j) > 0.8 Then
xry = xry + 1
ElseIf BestCI_ISX.Cells(i, j) <= 0.8 And BestCI_ISX.Cells(i, j) <> "" Then
xrr = xrr + 1
End If
R = R + 1


End If

Next j
Next i

TotalRacks = G + Y + R
End Sub[/vba]
[vba]Sub CI()
With Worksheets("Best CI ISX")
For i = 1 To nRow
For j = 1 To nCol

If Worksheets("Best CI CFD").Cells(i, j) >= 0.9 And _
Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xgg = xgg + 1

Case Is > 0.8
xgy = xgy + 1

Case Is <= 0.8
xgr = xgr + 1

End Select
G = G + 1

ElseIf Worksheets("Best CI CFD").Cells(i, j) < 9 _
And Worksheets("Best CI CFD").Cells(i, j) > 0.8 _
And Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xyg = xyg + 1

Case Is > 0.8
xyy = xyy + 1

Case Is <= 0.8
xyr = xyr + 1

End Select
Y = Y + 1

ElseIf Worksheets("Best CI CFD").Cells(i, j) < 0.8 _
And Worksheets("Best CI CFD").Cells(i, j) <> "" Then

Select Case Worksheets("Best CI ISX").Cells(i, j)

Case ""
Blank = Blank + 1 'for debug
'Do nothing

Case Is >= 0.9
xrg = xrg + 1

Case Is > 0.8
xry = xry + 1

Case Is <= 0.8
xrr = xrr + 1

End Select
R = R + 1

End If
Next j
Next i
End With


TotalRacks = G + Y + R
End Sub [/vba]

[Bob Phillips]

EDIT: I also found a nasty error. Fortunately, it did not affect the results at all.

Line [VBA]ElseIf BestCI_CFD.Cells(i, j) < 9 And BestCI_CFD.Cells(i, j) > 0.8 And BestCI_CFD.Cells ...etc[/VBA]

Should be 0.9 not 9 ...same thing in the select case.

That nasty error is not nasty at all, in fact it is totally irrelevant. You had already tested for >=0.9, so if it failed that it had to be <0.9, which is necessarily <9. As you also test for >=.8 in that line, the test for <0.9 can be scrapped, it is nt necessary.

[Saladsamurai]

p45cal: Beautiful. Those errors on my copy/paste part were what did it.

Thank you. My code looks so much better now.

(I almost look like I know what I'm doing :/ )