If statement not adding up correctly

**sted999** · 07-16-2010, 09:46 AM

Hiya all.

I am writing a macro to look a value in one column (Difference) which then adds one onto the appropriate variable. It is semi working, but the macro is adding what should be going on the P and M variable and is adding it on to the W variable.

Am i doing something completly silly? I cant seem to figure out what the problem is.

Thanks for any help.

If LOPNum > 0.5 Then
    PP = PP + 1
    Else
    If LOPNum = 0.4 Then
        P = P + 1
        Else
            If LOPNum = 0.3 Then
            P = P + 1
            Else
                If LOPNum = 0 Then
                W = W + 1
                Else
                    If LOPNum = -0.3 Then
                    M = M + 1
                    Else
                        If LOPNum = -0.4 Then
                        M = M + 1
                        Else
                            If LOPNum <= -0.6 Then
                            MM = MM + 1
                            End If
                        End If
                    End If
                End If
            End If
    End If
End If

**Marcol** · 07-16-2010, 10:27 AM

To make your code easier to follow, try using a Select statement rather than nested Ifs.
This is your code converted with no refinements

    Select Case LOPNum
        Case Is > 0.5
            PP = PP + 1
        Case 0.4
            P = P + 1
        Case 0.3
            P = P + 1
        Case 0
            W = W + 1
        Case -0.3
            M = M + 1
        Case -0.4
            M = M + 1
        Case Is <= -0.6
            MM = MM + 1
    End Select

Without the rest of your code it isn't clear what you are actually trying to do

...., but the macro is adding what should be going on the P and M variable and is adding it on to the W variable.

I can't see how this is possible from the snippet provided.

You might be better to post a desensitised workbook with the relevant code included.
so that we can better understand your aims.

**sted999** · 07-19-2010, 03:43 AM

Using CASE seemed to add everything to the P and PP variables only. I have attached the workbook.

Thanks.

**Marcol** · 07-19-2010, 04:40 AM

At first sight it seems your problem is how you have defined the variable LOPNum
It should be

Dim LOPNum As Double 'NOT as integer

There is also a fair number of redundant lines in your code.
I'll go through your code and do a bit of tidying and trimming.
Look back this afternoon I will have something for you.

**Marcol** · 07-19-2010, 10:36 AM

Try this

I have
1/. Added to your code the command to sort sheet "Data" first by Student ID then by Subject. Your code and my revision depend on this.
The yellow infill on this sheet is for demonstration purposes only, it doesn't affect the code.

2/. Re-written your code to cut out all unrequired duplicate commands and applied the results directly to their destinations without switching sheets each time.

3/. Fixed the Select case statement to cover the criteria as best as I can, you may have to alter the criteria to suit conditions I don't know about.

Switch to Sheet "LoP" and run via your button, no need to sort sheet data, as stated in point #1, the code does this for you.

Hope this helps

**sted999** · 07-19-2010, 10:54 AM

That is absolutely fantastic! Thanks very much for all your help.

I keep promising to teach myself VBA properly one day but its finding the time around all the other work I have to do - so far I’ve just been using a worksheet someone else created and editing the VBA in that.

Thanks again, you are a star.