Saladsamurai
09-22-2009, 05:58 AM
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:
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
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.
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
This produces the CORRECT Data:
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
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.
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