Problem with strings and a macro

[Guest]

Originally the macro used key1 in the lookup command with - lookrng1

However key1 could be the values MN, MS, FN, FS, .. (and this worked)

Now for one product I have to be more specific and be able to have MN0,
MN25, MP, MS0, ...

So I created a new key - key2 and thought that what I had would work and it
doesn't. Any help would be appreciated.


Sub Worksheet_Change(ByVal Target As Range)
Dim lookrng1 As Range
Dim lookrng2 As Range
Dim lookrng3 As Range
Dim lookrng4 As Range
Dim lookrng5 As Range
Dim key2 As String
' Application.EnableEvents = True
Select Case Range("prod1").Value
Case "Exp. Life Whole Life"
Set key2 = Range("Key1").Value
If Range("date").Value < #10/1/1998# Then
' Set lookrng1 = Worksheets("elwl").Range("C3:J79")
Set lookrng1 = Worksheets("elwl").Range("Y3:AF79")
Else
Set lookrng1 = Worksheets("elwl").Range("Y3:AF79")
End If
Set lookrng2 = Worksheets("elwl").Range("N3:U79")
If Left(Range("RatingInp").Value, 5) = "Table" Then
Set lookrng3 = Worksheets("Ratings").Range("C3:K89")
End If
Case "Exp. Life Term"
Set key2 = Range("Key1").Value
Set lookrng1 = Worksheets("eltm").Range("C3:J79")
Set lookrng2 = Worksheets("eltm").Range("N3:U79")
Case "Exp. Life PUWL"
Set key2 = Range("Key1").Value
If Range("date").Value < #1/1/1993# Then
Set lookrng1 = Worksheets("puwl").Range("C3:J79")
Else
Set lookrng1 = Worksheets("puwl").Range("N3:U79")
End If
Case "Named_Additional"
Set key2 = Range("Key1").Value
Set lookrng1 = Worksheets("eltm").Range("C3:J79")
Set lookrng2 = Worksheets("eltm").Range("N3:U79")
Case "LBD Whole Life"
Set lookrng1 = Worksheets("LBDWL").Range("B7:AK88")
Set lookrng2 = Worksheets("LBDWL").Range("AL7:AW88")
Select Case Right(Range("key1").Value)
Case "P"
Set key2 = Range("Key1").Value
Case "N", "S"
Select Case Range("UnitAmt").Value
Case Is < 25
Set key2 = Range("Key1").Value & "0"
Case 25 To 49.999
Set key2 = Range("Key1").Value & "25"
Case 50 To 99.999
Set key2 = Range("Key1").Value & "50"
Case Is >= 100
Set key2 = Range("Key1").Value & "100"
End Select
End Select
End Select
Set lookrng4 = Worksheets("gib").Range("C3:J79")
Set lookrng5 = Worksheets("sheet1").Range("g3:h79")

Application.EnableEvents = False
shtSummary.Unprotect

Range("PremAmt").Value = 0
Range("WaivAmt").Value = 0
Range("RatingOut").Value = 0
Range("GIBamt").Value = 0

Range("PremAmt").Value = Application.HLookup(key2, _
lookrng1, Range("issage").Value + 2, False)

If Range("prod1").Value = "Exp. Life PUWL" Then
Range("WaivInd").Value = "N"
End If
If Range("WaivInd").Value = "Y" Then
Range("WaivAmt").Value = Application.HLookup(Range("Key1").Value, _
lookrng2, Range("issage").Value + 2, False)
End If

Select Case Range("prod1").Value
Case "Exp. Life Whole Life"
If Left(Range("RatingInp").Value, 5) = "Table" Then
Range("RatingOut").Value =
Application.HLookup(Range("RatingInp").Value, _
lookrng3, Range("issage").Value + 2, False)
End If
Case "Exp. Life Term"
Select Case Range("RatingInp").Value
Case "Table_2"
Range("RatingOut").Value = Round(Range("PremAmt").Value
* 0.4, 2)
Case "Table_3"
Range("RatingOut").Value = Round(Range("PremAmt").Value
* 0.6, 2)
Case "Table_4"
Range("RatingOut").Value = Round(Range("PremAmt").Value
* 0.8, 2)
Case "Table_5"
Range("RatingOut").Value = Range("PremAmt").Value
Case "Table_6"
Range("RatingOut").Value = Round(Range("PremAmt").Value
* 1.2, 2)
Case "Table_8"
Range("RatingOut").Value = Round(Range("PremAmt").Value
* 1.6, 2)
Case Else
Range("RatingOut").Value = 0
End Select
End Select
Range("GIBamt").Value = Application.HLookup(Range("Key1").Value, _
lookrng4, Range("issage").Value + 2, False)

Select Case Range("prod1").Value
Case "Exp. Life Whole Life"
Module1.checkage
Case "Exp. Life Term"
Module1.checkage
Case "Exp. Life PUWL"
Range("WaivInd").Value = "N"
Range("RatingInp").Value = "N/A"
Range("RatingInp").Value = 0
Range("WaivInd").Locked = True
Range("UnitADB").Locked = False
Range("CTR_WP").Locked = False
Range("UnitCTR").Locked = False
Range("UnitGIB").Locked = False
Range("Cov_Date").Locked = False
Range("Date").Locked = False
Case "Named_Additional"
Range("Cov_Date").Value = Now()
Range("date").Value = Now()
Range("UnitADB").Value = 0
Range("UnitCTR").Value = 0
Range("UnitGIB").Value = 0
Range("UnitADB").Locked = True
Range("CTR_WP").Locked = True
Range("UnitCTR").Locked = True
Range("UnitGIB").Locked = True
Range("Cov_Date").Locked = True
Range("Date").Locked = True
End Select
If Range("c23").Value = "Y" Then
Range("E9").Locked = False
Range("E10").Locked = False
' Range("E11").Locked = False
Range("E12").Locked = False
Range("E14").Locked = False
Range("E15").Locked = False
Else
Range("E9").Locked = True
Range("E10").Locked = True
' Range("E11").Locked = True
Range("E12").Locked = True
Range("E14").Locked = True
Range("E15").Locked = True
End If
If Range("e12").Value = 0 Then

Else
Range("e14").Value = Application.VLookup(Range("c22").Value,
lookrng5, 2, False)
End If

' shtSummary.Protect
Application.EnableEvents = True
End Sub

 

[Guest]

The problem is that you are using the "set" command with a string - you need
to remove this and you will be fine