Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem with strings and a macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 846
Default Problem with strings and a macro

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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 846
Default Problem with strings and a macro
The problem is that you are using the "set" command with a string - you need
to remove this and you will be fine

"Brad" wrote:


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
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
problem with comparing strings in VBA [email protected] Excel Discussion (Misc queries) 2 June 25th 07 11:16 PM
Type mismatch error problem when dealing with Strings David Goodall[_2_] Excel Programming 4 June 30th 05 01:40 PM
Direction problem in inserting strings using right to left language Amir Excel Programming 1 June 10th 05 02:40 PM
XLL - VS .Net2003 - init problem - const char strings - /Gf option?? Kevin Love Excel Programming 0 July 27th 03 11:32 PM
XLL - VS .Net2003 - init problem - const char strings - /Gf option?? Rob Bovey Excel Programming 1 July 27th 03 10:41 PM


All times are GMT +1. The time now is 12:44 AM.

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