Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro not working correctly
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 846
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 857
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro not working correctly Andy_N1708 via OfficeKB.com Excel Discussion (Misc queries) 3 May 27th 10 03:35 AM
vlookup isn't working correctly? Dave F Excel Discussion (Misc queries) 2 October 14th 06 04:27 AM
Macro =sum function not working correctly crowdx42[_4_] Excel Programming 5 August 21st 06 05:47 AM
confused as to why macro is not working correctly Amanda Emily Excel Programming 1 August 2nd 06 06:39 PM
Function not working correctly Todd Huttenstine[_3_] Excel Programming 1 April 29th 04 02:33 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement