PDA

View Full Version : Solved: simplify the sintax for case + if



white_flag
12-21-2009, 08:32 AM
Hello,
I have the following code and I like to make it shorter, if this is possible. I mean less instruction (the same effect). in the "if" statement.
ex. IF <condition1, condition2, condition3 etc ...> AND <condition1, condition2, condition3 etc ...> THEN (instruction)

Select Case Range("$J$46").Value
Case Is = "AF"
If Range("$D$44").Value = 34 And Range("$E$44").Value = 9 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 42 And Range("$E$44").Value = 9 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 48 And Range("$E$44").Value = 9 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 34 And Range("$E$44").Value = 13 Then
Range("E58") = 0
Else
Range("E59") = formulaText
End If
Case Is = "Glass"
If Range("$D$44").Value >= 168 And Range("$E$44").Value = 30 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 27 And Range("$E$44").Value = 80 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 48 And Range("$E$44").Value = 90 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 48 And Range("$E$44").Value = 100 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 34 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 42 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 48 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 34 And Range("$E$44").Value = 30 Then
Range("E58") = 0
Else
Range("E58") = formulaText
End If
Case Else
If Range("$D$44").Value = 34 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 42 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value = 48 And Range("$E$44").Value = 25 Then
Range("E58") = 0
ElseIf Range("$D$44").Value <= 34 And Range("$E$44").Value = 30 Then
Range("E58") = 0
Else
Range("E59") = formulaText
End If
End Select

thank you

Bob Phillips
12-21-2009, 09:50 AM
Select Case Range("$J$46").Value
Case Is = "AF"
If (Range("$D$44").Value = 34 And Range("$E$44").Value = 9) Or _
(Range("$D$44").Value = 42 And Range("$E$44").Value = 9) Or _
(Range("$D$44").Value = 48 And Range("$E$44").Value = 9) Or _
(Range("$D$44").Value <= 34 And Range("$E$44").Value = 13) Then

Range("E58") = 0
Else

Range("E59") = formulaText
End If
Case Is = "Glass"
If (Range("$D$44").Value >= 168 And Range("$E$44").Value = 30) Or _
(Range("$D$44").Value <= 27 And Range("$E$44").Value = 80) Or _
(Range("$D$44").Value <= 48 And Range("$E$44").Value = 90) Or _
(Range("$D$44").Value <= 48 And Range("$E$44").Value = 100) Or _
(Range("$D$44").Value = 34 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value = 42 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value = 48 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value <= 34 And Range("$E$44").Value = 30) Then

Range("E58") = 0
Else

Range("E58") = formulaText
End If
Case Else

If (Range("$D$44").Value = 34 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value = 42 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value = 48 And Range("$E$44").Value = 25) Or _
(Range("$D$44").Value <= 34 And Range("$E$44").Value = 30) Then

Range("E58") = 0
Else

Range("E59") = formulaText
End If
End Select

white_flag
12-21-2009, 10:44 AM
@xld >> thank you for the reply. An point of view: some how can I use this

Select Case Range("$J$46").Value
Case Is = "AF"
If InStr("21 27 34 42 48 60", Range("$D$44").Value) Then
Range("E51") = 0
Else
Range("E51") = formulaText
End If
End Select

I like to add inside another condition, for an different range ex: Range("$E$44").Value with AND.. this is possible? if I put like this:
If InStr("21 27 34 42 48 60", Range("$D$44").Value) AND InStr("40 50 60 70 80 90 100", Range("$E$44").Value) Then
.. it is not going. based on your experience, you have an idea to do it correct?

thank you :)

Bob Phillips
12-21-2009, 11:00 AM
You could use



If Not IsError(Application.Match(Range("D44").Value, Array(21, 27, 34, 42, 48, 60), 0)) Then

white_flag
12-21-2009, 12:20 PM
for one range condition = TRUE, this is working perfect


If Not IsError(Application.Match(Range("$D$44").Value, Array(21, 27, 34, 42, 48, 60), 0)) Then
and

if InStr("21, 27, 34, 42, 48, 60", Range("$D$44").Value) Then

but wen I add the second condition with AND the second condition dasn't work
if range1.condition(values)= TRUE AND range2.condition(values)=TRUE then
code
instruction 1
else
instruction 2
it is an syntax instruction, that can help in this case?

Bob Phillips
12-21-2009, 03:03 PM
What does the failing code look like, and the data in question?

white_flag
12-21-2009, 03:08 PM
I don't have any failing code. Just the second part after AND dasn't do anything. The result of the code is ignoring the second condition whatever I do in the second Range.

Bob Phillips
12-21-2009, 03:24 PM
That's because it is bad code. That is looking for all of that string in the cell.

white_flag
12-21-2009, 04:07 PM
ok ..and an good code how will look like? or an idea of an good code for the second part?

Bob Phillips
12-21-2009, 05:58 PM
I already gave you the good code. It was a replacement for that.

white_flag
12-21-2009, 06:17 PM
I put like this:

Select Case Range("$J$46").Value
Case Is = "AF"
If Not IsError(Application.Match(Range("D44").Value, Array(21, 27, 34, 42, 48, 60), 0)) And IsError(Application.Match(Range("$E$44").Value, Array(25, 60, 70, 80, 90, 100), 0)) Then
Range("E51") = 0
Else
Range("E51") = "put formula"
End If
End Select

but it is not going. What I did wrong?

Bob Phillips
12-22-2009, 01:44 AM
It should both be NOT IsError.

white_flag
12-22-2009, 02:35 AM
I put also NOT in the syntax ..but, it is not working.
The second part is ignored.

Select Case Range("$J$46").Value
Case Is = "AF"
'If Not IsError(Application.Match(Range("$D44").Value, Array(21, 27, 34, 42, 48, 60), 0)) And Not InStr("25 60 70 80 90 100", Range("$E$44").Value) Then
'If Not InStr("21, 27, 34, 42, 48, 60", Range("$D$44").Value) And Not IsError(Application.Match(Range("$E$44").Value, Array("25 60 70 80 90 100"), 0)) Then
If Not IsError(Application.Match(Range("$D$44").Value, Array(21, 27, 34, 42, 48, 60), 0)) And Not IsError(Application.Match(Range("$E$44").Value, Array("25 60 70 80 90 100"), 0)) Then
Range("E51") = 0
Else
Range("E51") = "put formula"
End If
End Select

GTO
12-22-2009, 03:12 AM
Greetings white_flag,

You'll want to look up Array() in VBA help, and Match() in Excel help.

In the above, ditch the quote marks and add the commas to the arguments supplied Array(), as it needs to supply an array of values to Match, not a String.

Mark

white_flag
12-22-2009, 03:21 AM
correct :) ..my bad.

thank you very much, GTO (Mark) and off course xld
happy holidays
ps. this thread can be close now

GTO
12-22-2009, 03:25 AM
Merry Christmas to you as well :-)

As the originator of the thread, you mark it as Solved. Look under Thread Tools atop your first post.

Have a great day,

Mark

(You too Bob )