PDA

View Full Version : Solved: Syntax question



austenr
09-14-2010, 02:53 PM
Ive got code that checks for "Colgate" or "Hills" and somehow it falls into this IF statement. Obviously not coded right. What is correct?

If Sheets("Input + Wksheet").Range("G6").Value < 65 And Sheets("Input + Wksheet").Range("B10").Value = 1 _
And Sheets("Input + Wksheet").Range("B15").Value <> "Colgate" Or _
Sheets("Input + Wksheet").Range("B15").Value <> "Hills" Then

Bob Phillips
09-14-2010, 03:29 PM
Maybe



If Sheets("Input + Wksheet").Range("G6").Value < 65 And _
Sheets("Input + Wksheet").Range("B10").Value = 1 And _
Sheets("Input + Wksheet").Range("B15").Value <> "Colgate" And _
Sheets("Input + Wksheet").Range("B15").Value <> "Hills" Then

austenr
09-15-2010, 07:07 AM
Taking this a step further. I have three If statements (not nested). If the code satisfies the first one like the above post how can you code it to go to the end of the sub and skip the remaining two if the first one is met or the second one is met?

The third If is being executed because it meets the first two conditions above.

Bob Phillips
09-15-2010, 08:43 AM
In VBA you can't Austen. This is known as short-circuiting, and VBA doesn't short-circuit. So to achieve this, you have to nest them.

austenr
09-15-2010, 08:47 AM
Well doesnt hurt to ask Bob. Thanks

austenr
09-15-2010, 08:49 AM
How would you nest this correctly? Its executing the lines in red and its messing up the other If statements.

If Sheets("Input + Wksheet").Range("G6").Value < 65 And Sheets("Input + Wksheet").Range("B10").Value = 2 Then

With Sheets("Direct Bill ONLY")
DirectBillOnlyIndemnity1Off
DirectBillOnlyIndemnity2Off
DirectBillOnlyIndemnity3Off
DirectBillOnlyIndemnity4Off
DirectBillOnlyPPO3Off
DirectBillOnlyPPO4Off
DirectBillOnlyEPO3Off
DirectBillOnlyEPO4Off
.Range("I11").Value = "X"
' EE only
.Range("E32").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f32").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E32"
.Range("E40").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F40").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E40"
'EE & SP
.Range("J32").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K32").Formula = "=(PPO!H42 + PPO!L42)/12 - J32"
.Range("J40").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K40").Formula = "=(EPO!H42 + PPO!L42)/12 - J40"
End With

With Sheets("Direct Bill with RIA")
DirectBillOnlyRIAIndemnity1Off
DirectBillOnlyRIAIndemnity2Off
DirectBillOnlyRIAIndemnity3Off
DirectBillOnlyRIAIndemnity4Off
DirectBillOnlyRIAPPO3Off
DirectBillOnlyRIAPPO4Off
DirectBillOnlyRIAEPO3Off
DirectBillOnlyRIAEPO4Off
.Range("I11").Value = "X"
'EE only
.Range("E28").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f28").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E28"
.Range("E36").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F36").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E36"
'EE & SP
.Range("J28").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K28").Formula = "=(PPO!H42 + PPO!L42)/12 - J28"
.Range("J36").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K36").Formula = "=(EPO!H42 + PPO!L42)/12 - J36"

End With

End If


'************** Colgate 2's start here

If Sheets("Input + Wksheet").Range("B15").Value = "Colgate" _
And Sheets("Input + Wksheet").Range("D9").Value < DateValue("7/01/1996") And Sheets("Input + Wksheet").Range("B10").Value = 2 Then

With Sheets("Direct Bill ONLY")
DirectBillOnlyIndemnity3Off
DirectBillOnlyIndemnity4Off

DirectBillOnlyPPO3Off
DirectBillOnlyPPO4Off

DirectBillOnlyEPO3Off
DirectBillOnlyEPO4Off
.Range("I11").Value = "X"
' EE only
.Range("E24").Formula = "=if(MEDCVG=2,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F24").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E24"
'EE + SP
.Range("J24").Formula = "=(VLOOKUP(YearsOfService,Indemnity,7,FALSE) + VLOOKUP(YearsOfService,Indemnity,11,FALSE))/12"
.Range("K24").Formula = "=(Indemnity!H42 + Indemnity!L42)/12 - J24"
.Range("E32").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f32").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E32"
.Range("E40").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F40").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E40"
.Range("J32").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K32").Formula = "=(PPO!H42 + PPO!L42)/12 - J32"
.Range("J40").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K40").Formula = "=(EPO!H42 + PPO!L42)/12 - J40"
End With


With Sheets("Direct Bill with RIA")
DirectBillOnlyRIAIndemnity3Off
DirectBillOnlyRIAIndemnity4Off

DirectBillOnlyRIAPPO3Off
DirectBillOnlyRIAPPO4Off

DirectBillOnlyRIAEPO3Off
DirectBillOnlyRIAEPO4Off
.Range("I11").Value = "X"
'EE only
.Range("E20").Formula = "=if(MEDCVG=2,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F20").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E20"
'EE + SP
.Range("J20").Formula = "=(VLOOKUP(YearsOfService,Indemnity,7,FALSE) + VLOOKUP(YearsOfService,Indemnity,11,FALSE))/12"
.Range("K20").Formula = "=(Indemnity!H42 + Indemnity!L42)/12 - J20"
.Range("E28").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f28").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E28"
.Range("E36").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F36").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E36"
'EE & SP
.Range("J28").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K28").Formula = "=(PPO!H42 + PPO!L42)/12 - J28"
.Range("J36").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K36").Formula = "=(EPO!H42 + PPO!L42)/12 - J36"
End With
End If


'******************* Hills 2's start here

If Sheets("Input + Wksheet").Range("B15").Value = "Hills" And Sheets("Input + Wksheet").Range("D9").Value < DateValue("7/01/2000") _
And Sheets("Input + Wksheet").Range("B10").Value = 2 Then

With Sheets("Direct Bill ONLY")
DirectBillOnlyIndemnity3Off
DirectBillOnlyIndemnity4Off

DirectBillOnlyPPO3Off
DirectBillOnlyPPO4Off

DirectBillOnlyEPO3Off
DirectBillOnlyEPO4Off
.Range("I11").Value = "X"
.Range("E24").Formula = "=if(MEDCVG=2,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F24").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E24"
' EE SP
.Range("J24").Formula = "=(VLOOKUP(YearsOfService,Indemnity,7,FALSE) + VLOOKUP(YearsOfService,Indemnity,11,FALSE))/12"
.Range("K24").Formula = "=(Indemnity!H42 + Indemnity!L42)/12 - J24"
.Range("E32").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f32").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E32"
.Range("E40").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F40").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E40"
.Range("J32").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K32").Formula = "=(PPO!H42 + PPO!L42)/12 - J32"
.Range("J40").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K40").Formula = "=(EPO!H42 + PPO!L42)/12 - J40"
End With

With Sheets("Direct Bill with RIA")
DirectBillOnlyRIAIndemnity3Off
DirectBillOnlyRIAIndemnity4Off

DirectBillOnlyRIAPPO3Off
DirectBillOnlyRIAPPO4Off

DirectBillOnlyRIAEPO3Off
DirectBillOnlyRIAEPO4Off
.Range("I11").Value = "X"
.Range("E20").Formula = "=if(MEDCVG=2,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F20").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E20"
'EE + SP
.Range("J20").Formula = "=(VLOOKUP(YearsOfService,Indemnity,7,FALSE) + VLOOKUP(YearsOfService,Indemnity,11,FALSE))/12"
.Range("K20").Formula = "=(Indemnity!H42 + Indemnity!L42)/12 - J20"
.Range("E28").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,PPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("f28").Formula = "=IF(Age<65,PPO!H42/12,PPO!O42/12)-E28"
.Range("E36").Formula = "=IF(MEDCVG=2,VLOOKUP(YearsOfService,EPO,IF(Age<=65,7,14),FALSE),0)/12"
.Range("F36").Formula = "=IF(Age<65,EPO!H42/12,EPO!O42/12)-E36"
'EE & SP
.Range("J28").Formula = "=(VLOOKUP(YearsOfService,PPO,11,FALSE) + VLOOKUP(YearsOfService,PPO,7,FALSE))/12"
.Range("K28").Formula = "=(PPO!H42 + PPO!L42)/12 - J28"
.Range("J36").Formula = "=(VLOOKUP(YearsOfService,EPO,7,FALSE) + VLOOKUP(YearsOfService,EPO,11,FALSE))/12"
.Range("K36").Formula = "=(EPO!H42 + PPO!L42)/12 - J36"
End With

End If

Bob Phillips
09-15-2010, 10:24 AM
Which bit?

Kenneth Hobs
09-15-2010, 11:35 AM
Use debug, F8, to see why the code stopped where it did.

Maybe I read your other question wrong. I would have thought that Exit Sub would meet your needs. One could use GoSub but I generally avoid that method. Another method might be Select Case. Notice how the If routine will never show the last MsgBox if x<=10.

Sub Test_Select_Case()
Dim x As Integer
x = 5
Select Case (True)
Case x < 0
MsgBox x & " < 0 = True"
Case x >= 0 And x < 3
MsgBox x & " >= 0 And " & x & " < 3 = True"
Case x >= 3 And x <= 10
MsgBox x & " >= 3 And " & x & " <= 10 = True"
Case Else
MsgBox x & " > 10 = True"
End Select
MsgBox "Test_Select_Case executed at end."
End Sub

Sub Test_IF_Exit_Sub()
Dim x As Integer
x = 5
If x < 0 Then
MsgBox x & " < 0 = True"
Exit Sub
End If
If x >= 0 And x < 3 Then
MsgBox x & " >= 0 And " & x & " < 3 = True"
Exit Sub
End If
If x >= 3 And x <= 10 Then
MsgBox x & " >= 3 And " & x & " <= 10 = True"
Exit Sub
End If
MsgBox "Test_IF_Exit_Sub executed at end."
End Sub

austenr
09-15-2010, 11:54 AM
Thanks guys i got it worked out.