PDA

View Full Version : Solved: I am the Worst:: Something is Terribly Wrong w/ My Code



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

mbarron
08-26-2009, 07:29 PM
You are referencing the wrong sheets.
"Best CI CFD" is sheet 8, not sheet 10 and "Best CI ISX" is sheet 7 not sheet 9

Making these changes xrg = 132

Saladsamurai
08-26-2009, 07:34 PM
You are referencing the wrong sheets.
"Best CI CFD" is sheet 8, not sheet 10 and "Best CI ISX" is sheet 7 not sheet 9

Making these changes xrg = 132
Not sure where you are getting that? :dunno

In the Project Manager, It definitely says :
Sheet9 (Best CI ISX)
Sheet10(Best CI CFD)

http://i12.photobucket.com/albums/a220/saladsamurai/11111111.jpg


EDIT: ARe you saying that Excel WorkSheet Indices are based on what order they appear in inside the WorkBook? And not the Sheet Number in the project manager?

EDIT2: Oh my god; you are. I just tested it with a MsgBox.

Can somebody please tell what why on earth that is the logic? I am not knocking it, I just don't understand why the indices would not just be assigned as the Worksheets were created?

That way you could reference by index and not have to worry about what happens when you drag a Worksheet tab around to reorganize the WorkBook.

(As in this case!!!)

EDIT3: BTW Thanks for the save mbarron!!!!!!! :friends:

mbarron
08-26-2009, 07:39 PM
The index number of a worksheet is assigned left to right as the tabs are shown in the workbook. The Sheet# showing in the Project Manager is not taken into consideration. Move the two sheets in question to the right of the two Temp sheets and your macro will fire correctly (or change the index numbers as first suggested).

mbarron
08-26-2009, 07:52 PM
To use the name of the sheet as shown in the Project Explorer, you can reference by using the portion before parenthesis.

Sheet1(Name of Sheet) can be referenced as Sheet12


In your code

ElseIf Worksheets(9).Cells(i, j)...

would become

ElseIf Sheet9.Cells(i, j)....

Saladsamurai
08-27-2009, 05:06 AM
To use the name of the sheet as shown in the Project Explorer, you can reference by using the portion before parenthesis.

Sheet1(Name of Sheet) can be referenced as Sheet12


In your code

ElseIf Worksheets(9).Cells(i, j)...

would become

ElseIf Sheet9.Cells(i, j)....

Ah. Good Tip! Thank you :)