PDA

View Full Version : Solved: What stops this code from working?


Aussiebear
12-19-2007, 07:32 AM
On the "Risk Levels" sheet I have a button New Residue which brings to life a form, that allows the user to enter new residue results. From this the code is meant to change the risk level of a vendor under the following rules;

1. Two sequential "<LOR" results should change a vendors risk level to "0",
2. Either "<AL" or ">AL" result should change a vendors risk level to their initial risk level as in column I,( Either "1" or "2"), or retain their current level if already a "1" or "2'".
3. A ">MRL" result should change the current risk level to a "3".

This is no longer happening, and I cannot understand why. When I entered the residue results for T2727-13-250 as "<LOR" the risk level for Grower 888 (Acheson Rural Services) should have chaged to "0" but is retaining the initial risk level of "1".

The following section of code is what I currently have to adjust the risk level

Sub AdjustRiskLevels(Tgt As Range)
Dim Col As Long
'Adjust risk level value for result
Col = Application.WorksheetFunction.CountBlank(Tgt.Range("J1:L1"))
Select Case Me.cboResult

Case ">MRL"
Tgt.Range("M1") = 3

Case "<AL", ">AL"
If Tgt.Range("M1") = 0 Then Tgt.Range("M1") = 1
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = Tgt.Range("I1")
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = 3


Case "<LOR"
If Tgt.Range("M1") = 0 Then
'No Change
Else
Select Case Col
Case Is < 2
If Tgt.Range("K2") = "<LOR" And Tgt.Range("L2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = 0
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = Tgt.Range("I1")
End If
Case 2
If Tgt.Range("J2") = "<LOR" And Tgt.Range("K2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = 0
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = Tgt.Range("I1")
End If

End Select
End If
End Select

End Sub


Could someone please tell me why this is so?

To view all of the code, you will need to enter the password "Shona"
unmarkedhelicopter
12-19-2007, 07:45 AM
You can't put ">MRL" in a case statement
Try :-
Sub AdjustRiskLevels(Tgt As Range)
Dim Col As Long
'Adjust risk level value for result
Col = Application.WorksheetFunction.CountBlank(Tgt.Range("J1:L1"))
Select Case True

Case Me.cboResult > "MRL"
Tgt.Range("M1") = 3

Case Me.cboResult <"AL", Me.cboResult < "AL"
If Tgt.Range("M1") = 0 Then Tgt.Range("M1") = 1
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = Tgt.Range("I1")
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = 3


Case Me.cboResult < "LOR"
If Tgt.Range("M1") = 0 Then
'No Change
Else
Select Case Col
Case Is < 2
If Tgt.Range("K2") = "<LOR" And Tgt.Range("L2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = 0
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = Tgt.Range("I1")
End If
Case 2
If Tgt.Range("J2") = "<LOR" And Tgt.Range("K2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then Tgt.Range("M1") = 0
If Tgt.Range("M1") = 3 Then Tgt.Range("M1") = Tgt.Range("I1")
End If

End Select
End If
End Select

End Sub
Though looking at you code if it ain't greater than MRL then it WILL be either greater or less than AL unless it is AL and then that won't pass LOR so it seems screwy to me.
RonMcK
12-19-2007, 11:31 AM
Unmarkedhelicopter:

Why can't Aussiebear test using the text strings "<LOR", ">MRL", ">AL", and "<AL" in his select case/end select block?


Aussiebear:

1. I believe that you need to nest your IF statements so only one of the choices will be chosen and executed. (see my VBA paste, below.)

2. Your example item is on Row 3 of your worksheet,

3. However, the formulas use cells I1, J1, (K1), L1, and M1 (in heading Row 1), cells J2, K2, and L2 (part of the hidden Row 2) and no cells on Row 3.

Are the row references in your formulas correct for your sample?

4. Your worksheet has Risk Level in column F, however, you appear to use M1 for risk level in your formulas. Is this intentional?

5. On Row 3, Cells I, J, K, L, and M contain the following data (values from sample):

I - Delivered Tonnage (1,150.0) not likely to be in range 1..3
J - 1st Test Called (Y) never <LOR, >MRL, <AL, or >AL
K - 1st Test Result (<LOR)
L - 250 Test Called (Y) never <LOR, >MRL, <AL, or >AL
M - 250 Rest Result (<LOR) never in range 1..3
Am I misinterpreting your notes and formulas?

Sub AdjustRiskLevels(Tgt As Range)
Dim Col As Long
'Adjust risk level value for result
Col = Application.WorksheetFunction.CountBlank(Tgt.Range("J1:L1"))
Select Case Me.cboResult

Case ">MRL"
Tgt.Range("M1") = 3

Case "<AL", ">AL"
If Tgt.Range("M1") = 0 Then
Tgt.Range("M1") = 1
ElseIf Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = 3
End If

Case "<LOR"
If Tgt.Range("M1") <> 0 Then
Select Case Col
Case Is < 2
If Tgt.Range("K2") = "<LOR" And Tgt.Range("L2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = 0
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
End If
Case 2
If Tgt.Range("J2") = "<LOR" And Tgt.Range("K2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = 0
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
End If

End Select
End If
End Select

End Sub




Thanks,

Ron
Orlando, FL
unmarkedhelicopter
12-19-2007, 11:50 AM
Ron, you are correct if the string he is testing for is actually ">MRL" etc. Aussiebear ... are you ?
Bob Phillips
12-19-2007, 11:53 AM
Of course he is, that is clear from the text, nd from the worksheet.
unmarkedhelicopter
12-19-2007, 12:18 PM
Ah !
Sorry I did not look at the sheet.
Stands to reason that would land me in the c.r.a.p. (I wonder if the input filters will find that ?)
Aussiebear
12-19-2007, 12:33 PM
Late for work, so this'll be super quick.

UMH: Yes I am testing for strings "<LOR", "<AL", ">AL" & ">MRL".

Ron: Column I on the risk levels page is Initial Risk Value and is based on an assesment of the Vendor type which is done independantly from myself.

Columns J, L, N, P, R & T of the Whiteboard sheet are formulae to question if a test needs to be called ( tons recieved exceeds a certain value). Columns K, M, O, Q, S & U are string values ( includes blank). Blank when a "Y" in a preceeding column tells me that a sample needs to be sent away for analysis, an "S" indicates that a sample has been sent away for analysis but a result has not yet been recieved, a string "<LOR", "<AL", ">AL" or ">MRL" indicates the result of any residue if found in the sample.

Will have a better look at your responces tonight, so if I'm misleading anybody at this moment I apologise.....
Aussiebear
12-20-2007, 07:24 AM
Ron, I'm getting a compile error "Case without Case Select" error message and it refers to the line "Case 2"

I've tried "Case Is 2" but it then errors out saying it expects <, or >, or <>, or = type definitions. "Case = 2" is changed to Case Is = 2 by the software but is still erroring out.
rory
12-20-2007, 07:58 AM
I think it should be:
Sub AdjustRiskLevels(Tgt As Range)
Dim Col As Long
'Adjust risk level value for result
Col = Application.WorksheetFunction.CountBlank(Tgt.Range("J1:L1"))
Select Case cboResult

Case ">MRL"
Tgt.Range("M1") = 3

Case "<AL", ">AL"
If Tgt.Range("M1") = 0 Then
Tgt.Range("M1") = 1
ElseIf Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = 3
End If

Case "<LOR"
If Tgt.Range("M1") <> 0 Then
Select Case Col
Case Is < 2
If Tgt.Range("K2") = "<LOR" And Tgt.Range("L2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = 0
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
End If
End If
Case 2
If Tgt.Range("J2") = "<LOR" And Tgt.Range("K2") = "<LOR" Then
If Tgt.Range("M1") < 3 Then
Tgt.Range("M1") = 0
ElseIf Tgt.Range("M1") = 3 Then
Tgt.Range("M1") = Tgt.Range("I1")
End If
End If
End Select
End If
End Select
End Sub
Aussiebear
12-20-2007, 09:11 AM
Thanks Rory, this fixes the compile error but still leaves me with an operational error.

If J2 & K2 values are "<LOR" the value in M1 changes to "0" as it should, however if J2 was not "<LOR" and K2 & L2 values are "<LOR", the value in M1 does not change. :banghead:

My understanding of the 1st part of the code is that it counts the blanks in the range J1:L1 and if there is less than 2 ( which means J1 & K1 must be filled) then do something based on the values in J2 & K2. Providing these values are "<LOR" then M1 should change to a "0"?

I'm wondering then, that where the code reads Case 2, the logic fails because only J1 is filled and I don't have 2 sequential "<LOR" results.

...or am I readig this wrong?
Aussiebear
12-21-2007, 05:21 AM
okay, you can all call me "Slow"....

Zoomed off to work this morning and changed the Case bits and then it hit me. You are referring to the wrong cells changed them and bingo, its all bells and whistles working.

Will post the correct code on Monday night (Aussie time). My thanks to all who assisted.

:clap: