ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro not working correctly (https://www.excelbanter.com/excel-programming/386170-macro-not-working-correctly.html)

Brad
Macro not working correctly
 
I do not understand why the case statement evaluating IssAge is not working -
everything else is. Any help would be greatly appreciated. The cells that
I'm trying to protect remain unprotected. IssAge is a calculated field.
=a1+b1-c1 (example)


Sub Worksheet_Change(ByVal Target As Range)
Dim lookrng1 As Range
Dim lookrng2 As Range
Dim lookrng3 As Range
Dim lookrng4 As Range
' Application.EnableEvents = True
If Range("prod1").Value = "Exp. Life Whole Life" Then
shtSummary.Unprotect
If Range("date").Value < #10/1/1998# Then
Set lookrng1 = Worksheets("elwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("elwl").Range("Y3:AF79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
Set lookrng2 = Worksheets("elwl").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
If Left(Range("RatingInp").Value, 5) = "Table" Then
Set lookrng3 = Worksheets("Ratings").Range("C3:K89")
Application.EnableEvents = False
Range("RatingOut").Value =
Application.HLookup(Range("RatingInp").Value, _
lookrng3, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("RatingOut").Value = 0
Application.EnableEvents = True
End If
shtSummary.Protect
End If
If Range("prod1").Value = "Exp. Life Term" Then
shtSummary.Unprotect
Set lookrng1 = Worksheets("eltm").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Set lookrng2 = Worksheets("eltm").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
Application.EnableEvents = False
Select Case Range("RatingInp").Value
Case Is = "Table_2"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.4, 2)
Case Is = "Table_3"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.6, 2)
Case Is = "Table_4"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.8, 2)
Case Is = "Table_5"
Range("RatingOut").Value = Range("PremAmt").Value
Case Is = "Table_6"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.2, 2)
Case Is = "Table_8"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.6, 2)
Case Else
Range("RatingOut").Value = 0
End Select
shtSummary.Protect
Application.EnableEvents = True
End If
If Range("prod1").Value = "Exp. Life PUWL" Then
shtSummary.Unprotect
Application.EnableEvents = False
Range("WaivInd").Value = "N"
Range("RatingInp").Value = "N/A"
Range("RatingOut").Value = 0
Application.EnableEvents = True

If Range("date").Value < #1/1/1993# Then
Set lookrng1 = Worksheets("puwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("puwl").Range("N3:U79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
shtSummary.Protect
End If
Select Case Range("IssAge").Value
Case Is < 36
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = False
Case 36 To 55
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = True
Case Is 55
shtSummary.Unprotect
Range("WaivInd").Locked = True
Range("WP_mult").Locked = True
Range("UnitADB").Locked = True
Range("c20").Locked = True
Range("UnitCTR").Locked = True
Range("UnitGIB").Locked = True
End Select
shtSummary.Protect

Application.EnableEvents = False
shtSummary.Unprotect
Set lookrng4 = Worksheets("gib").Range("C3:J79")
Range("GIBamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng4, Range("issage").Value + 2, False)
shtSummary.Protect
Application.EnableEvents = True
End Sub

Tom Ogilvy
Macro not working correctly
 
Just to save the OP some time,

"Case is" worked fine for me:

Sub TestIssAge()
Select Case Range("IssAge").Value
Case Is < 36
Debug.Print "<36"
Case 36 To 55
Debug.Print "between 36 and 55"
Case Is 55
Debug.Print "55"
Case Else
Debug.Print Range("issAge").Value
Debug.Print Range("issAge").Value < 36
Debug.Print Range("issAge").Value = 36 And _
Range("IssAge").Value <= 55
Debug.Print Range("issAge").Value 55
Debug.Print TypeName(Range("IssAge").Value)
End Select

End Sub

Booleans were less than 36
Strings were greater than 55
errors returned type mismatch
Numbers worked fine.



--
Regards,
Tom Ogilvy


"Vergel Adriano" wrote:

Brad,

Instead of this way:

Case Is = "Table_2"

try it like this:

Case "Table_2"


apply the same syntax change in the other Case conditions.


--
Hope that helps.

Vergel Adriano


"Brad" wrote:

I do not understand why the case statement evaluating IssAge is not working -
everything else is. Any help would be greatly appreciated. The cells that
I'm trying to protect remain unprotected. IssAge is a calculated field.
=a1+b1-c1 (example)


Sub Worksheet_Change(ByVal Target As Range)
Dim lookrng1 As Range
Dim lookrng2 As Range
Dim lookrng3 As Range
Dim lookrng4 As Range
' Application.EnableEvents = True
If Range("prod1").Value = "Exp. Life Whole Life" Then
shtSummary.Unprotect
If Range("date").Value < #10/1/1998# Then
Set lookrng1 = Worksheets("elwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("elwl").Range("Y3:AF79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
Set lookrng2 = Worksheets("elwl").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
If Left(Range("RatingInp").Value, 5) = "Table" Then
Set lookrng3 = Worksheets("Ratings").Range("C3:K89")
Application.EnableEvents = False
Range("RatingOut").Value =
Application.HLookup(Range("RatingInp").Value, _
lookrng3, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("RatingOut").Value = 0
Application.EnableEvents = True
End If
shtSummary.Protect
End If
If Range("prod1").Value = "Exp. Life Term" Then
shtSummary.Unprotect
Set lookrng1 = Worksheets("eltm").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Set lookrng2 = Worksheets("eltm").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
Application.EnableEvents = False
Select Case Range("RatingInp").Value
Case Is = "Table_2"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.4, 2)
Case Is = "Table_3"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.6, 2)
Case Is = "Table_4"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.8, 2)
Case Is = "Table_5"
Range("RatingOut").Value = Range("PremAmt").Value
Case Is = "Table_6"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.2, 2)
Case Is = "Table_8"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.6, 2)
Case Else
Range("RatingOut").Value = 0
End Select
shtSummary.Protect
Application.EnableEvents = True
End If
If Range("prod1").Value = "Exp. Life PUWL" Then
shtSummary.Unprotect
Application.EnableEvents = False
Range("WaivInd").Value = "N"
Range("RatingInp").Value = "N/A"
Range("RatingOut").Value = 0
Application.EnableEvents = True

If Range("date").Value < #1/1/1993# Then
Set lookrng1 = Worksheets("puwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("puwl").Range("N3:U79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
shtSummary.Protect
End If
Select Case Range("IssAge").Value
Case Is < 36
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = False
Case 36 To 55
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = True
Case Is 55
shtSummary.Unprotect
Range("WaivInd").Locked = True
Range("WP_mult").Locked = True
Range("UnitADB").Locked = True
Range("c20").Locked = True
Range("UnitCTR").Locked = True
Range("UnitGIB").Locked = True
End Select
shtSummary.Protect

Application.EnableEvents = False
shtSummary.Unprotect
Set lookrng4 = Worksheets("gib").Range("C3:J79")
Range("GIBamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng4, Range("issage").Value + 2, False)
shtSummary.Protect
Application.EnableEvents = True
End Sub

Vergel Adriano
Macro not working correctly
 
Brad,

Instead of this way:

Case Is = "Table_2"

try it like this:

Case "Table_2"


apply the same syntax change in the other Case conditions.


--
Hope that helps.

Vergel Adriano


"Brad" wrote:

I do not understand why the case statement evaluating IssAge is not working -
everything else is. Any help would be greatly appreciated. The cells that
I'm trying to protect remain unprotected. IssAge is a calculated field.
=a1+b1-c1 (example)


Sub Worksheet_Change(ByVal Target As Range)
Dim lookrng1 As Range
Dim lookrng2 As Range
Dim lookrng3 As Range
Dim lookrng4 As Range
' Application.EnableEvents = True
If Range("prod1").Value = "Exp. Life Whole Life" Then
shtSummary.Unprotect
If Range("date").Value < #10/1/1998# Then
Set lookrng1 = Worksheets("elwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("elwl").Range("Y3:AF79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
Set lookrng2 = Worksheets("elwl").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
If Left(Range("RatingInp").Value, 5) = "Table" Then
Set lookrng3 = Worksheets("Ratings").Range("C3:K89")
Application.EnableEvents = False
Range("RatingOut").Value =
Application.HLookup(Range("RatingInp").Value, _
lookrng3, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("RatingOut").Value = 0
Application.EnableEvents = True
End If
shtSummary.Protect
End If
If Range("prod1").Value = "Exp. Life Term" Then
shtSummary.Unprotect
Set lookrng1 = Worksheets("eltm").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Set lookrng2 = Worksheets("eltm").Range("N3:U79")
If Range("WaivInd").Value = "Y" Then
Application.EnableEvents = False
Range("WaivAmt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("WaivAmt").Value = 0
Application.EnableEvents = True
End If
Application.EnableEvents = False
Select Case Range("RatingInp").Value
Case Is = "Table_2"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.4, 2)
Case Is = "Table_3"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.6, 2)
Case Is = "Table_4"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
0.8, 2)
Case Is = "Table_5"
Range("RatingOut").Value = Range("PremAmt").Value
Case Is = "Table_6"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.2, 2)
Case Is = "Table_8"
Range("RatingOut").Value = Round(Range("PremAmt").Value *
1.6, 2)
Case Else
Range("RatingOut").Value = 0
End Select
shtSummary.Protect
Application.EnableEvents = True
End If
If Range("prod1").Value = "Exp. Life PUWL" Then
shtSummary.Unprotect
Application.EnableEvents = False
Range("WaivInd").Value = "N"
Range("RatingInp").Value = "N/A"
Range("RatingOut").Value = 0
Application.EnableEvents = True

If Range("date").Value < #1/1/1993# Then
Set lookrng1 = Worksheets("puwl").Range("C3:J79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
Else
Set lookrng1 = Worksheets("puwl").Range("N3:U79")
Application.EnableEvents = False
Range("Premamt").Value =
Application.HLookup(Range("Key1").Value, _
lookrng1, Range("issage").Value + 2, False)
Application.EnableEvents = True
End If
shtSummary.Protect
End If
Select Case Range("IssAge").Value
Case Is < 36
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = False
Case 36 To 55
shtSummary.Unprotect
Range("WaivInd").Locked = False
Range("WP_mult").Locked = False
Range("UnitADB").Locked = False
Range("c20").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = True
Case Is 55
shtSummary.Unprotect
Range("WaivInd").Locked = True
Range("WP_mult").Locked = True
Range("UnitADB").Locked = True
Range("c20").Locked = True
Range("UnitCTR").Locked = True
Range("UnitGIB").Locked = True
End Select
shtSummary.Protect

Application.EnableEvents = False
shtSummary.Unprotect
Set lookrng4 = Worksheets("gib").Range("C3:J79")
Range("GIBamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng4, Range("issage").Value + 2, False)
shtSummary.Protect
Application.EnableEvents = True
End Sub


All times are GMT +1. The time now is 11:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com