Saladsamurai
08-26-2009, 06:55 PM
:banghead: OK. Here is the deal. I wrote this code as a means of comparing the Data in WorkSheets(9) ["Best CI ISX"] with the Data in WorkSheets(10) ["Best CI CFD"]
I have attached the WrkBk.
Here is what is happening.
The variables work like this: xgg means WorkSheets(10).Cells(i,j) is <=.9
and WorkSheets(9) is also <=.9
In other words it counts the number of times (x) CFD predicts <.9 (green) and ISX predicts (green)
The rest of the variables follow a similar naming scheme, but nevermind all that.
Let's look at an example of what is going wrong.
When I MsgBox xgg , I get a number (451), but when I message box any of the others I get 0.
Clearly not all of WorkSheets(10) AND WorkSheets(9) cells are <90%
Looking at WorkSheets(10) ["Best CI CFD"] in cell F20 we see that its value is 2%
while WorkSheets(9) ["Best CI ISX"] is 93%
Clearly this falls in the category of "xrg" .... i.e. WorkSheets(10) < .80 And WorkSheets(9) >=.9
But when I MsgBox xrg it says 0.
Option Explicit
Private Sub CommandButton1_Click()
Dim i, j, G, Y, R As Integer
Dim xgg, xgy, xgr, xyg, xyy, xyr, xrg, xry, xrr As Integer
xgg = 0
xgy = 0
xgr = 0
xyg = 0
xyy = 0
xyr = 0
xrg = 0
xry = 0
xrr = 0
G = 0
Y = 0
R = 0
For i = 1 To 25
For j = 1 To 32
If Worksheets(10).Cells(i, j) >= 0.9 Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xgr = xgr + 1
End If
G = G + 1
ElseIf Worksheets(10).Cells(i, j) < 9 And Worksheets(10).Cells(i, j) > 0.8 Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xyg = xyg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xyy = xyy + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xyr = xyr + 1
End If
Y = Y + 1
ElseIf Worksheets(10).Cells(i, j) <= 0.8 And Worksheets(10).Cells(i, j) <> "" Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xrg = xrg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xry = xry + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xrr = xrr + 1
End If
R = R + 1
End If
Next j
Next i
MsgBox xrg
End Sub
I am praying to the VBA gods here : pray2::help
I have attached the WrkBk.
Here is what is happening.
The variables work like this: xgg means WorkSheets(10).Cells(i,j) is <=.9
and WorkSheets(9) is also <=.9
In other words it counts the number of times (x) CFD predicts <.9 (green) and ISX predicts (green)
The rest of the variables follow a similar naming scheme, but nevermind all that.
Let's look at an example of what is going wrong.
When I MsgBox xgg , I get a number (451), but when I message box any of the others I get 0.
Clearly not all of WorkSheets(10) AND WorkSheets(9) cells are <90%
Looking at WorkSheets(10) ["Best CI CFD"] in cell F20 we see that its value is 2%
while WorkSheets(9) ["Best CI ISX"] is 93%
Clearly this falls in the category of "xrg" .... i.e. WorkSheets(10) < .80 And WorkSheets(9) >=.9
But when I MsgBox xrg it says 0.
Option Explicit
Private Sub CommandButton1_Click()
Dim i, j, G, Y, R As Integer
Dim xgg, xgy, xgr, xyg, xyy, xyr, xrg, xry, xrr As Integer
xgg = 0
xgy = 0
xgr = 0
xyg = 0
xyy = 0
xyr = 0
xrg = 0
xry = 0
xrr = 0
G = 0
Y = 0
R = 0
For i = 1 To 25
For j = 1 To 32
If Worksheets(10).Cells(i, j) >= 0.9 Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xgg = xgg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xgy = xgy + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xgr = xgr + 1
End If
G = G + 1
ElseIf Worksheets(10).Cells(i, j) < 9 And Worksheets(10).Cells(i, j) > 0.8 Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xyg = xyg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xyy = xyy + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xyr = xyr + 1
End If
Y = Y + 1
ElseIf Worksheets(10).Cells(i, j) <= 0.8 And Worksheets(10).Cells(i, j) <> "" Then
If Worksheets(9).Cells(i, j) >= 0.9 Then
xrg = xrg + 1
ElseIf Worksheets(9).Cells(i, j) < 0.9 And Worksheets(9).Cells(i, j) > 0.8 Then
xry = xry + 1
ElseIf Worksheets(9).Cells(i, j) <= 0.8 And Worksheets(9).Cells(i, j) <> "" Then
xrr = xrr + 1
End If
R = R + 1
End If
Next j
Next i
MsgBox xrg
End Sub
I am praying to the VBA gods here : pray2::help