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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.