Thread: Pricing labels

Hello Again,

Iam need to prepare an template for medical underwriting pricing tool.

Based on these following above first 4 fields it should calculate NRP then based on nationality the loading or discount will be appear in columns on sheet Template.

Original sheet is attached for your reference.

Please can anyone help me to make this file work as per requirement.

Thanks in advance for your help.

So from which sheet should the discount parameters be taken?

What Is NRP and how is it calculated?

It will be good if you enter in some calculations in the template tab so we can find out how the calculations are done.

Kindly check the sample which i have done manually for 3 records. I hope this will help you to understand my requirement.

Here is a code that should work, Its ugly and slow but that has todo with my limited knownledge in VBA, It works (i think as you want) and you can use it until you get a better version from someone.

Here is a code that should work, Its ugly and slow but that has todo with my limited knownledge in VBA, It works (i think as you want) and you can use it until you get a better version from someone.

I have problem in calculation. Kindly see the example below. it is taking nrp same for all the ages.

i have reviewed your script but did not find any errors. I hope you can help me in this problem.

updated sheet is attached for your reference.

Hey, Try this

Sub d()
  
  Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
  Dim cel As Range, cel2 As Range, cel3 As Range, cel4 As Range, found As Range, rng As Range, age As Variant
  Dim a As Range, b As Range, c As Range, d As Range, cel5 As Range
  Dim a1, b1, c1, d1, e1
  Dim i&, y&, LR&
      Set ws = Sheets("Aleem")
      Set ws1 = Sheets("Calculation Data")
      Set ws2 = Sheets("Template")
      LR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
   Application.ScreenUpdating = 0
      With ws1
       For i = 2 To LR
         Set a = ws2.Cells(i, 2)
         Set b = ws2.Cells(i, 4)
         Set c = ws2.Cells(i, 6)
           ws2.Cells(i, 1).Resize(, 6).Value = ws.Cells(i, 1).Resize(, 6).Value
           Set rng = ws2.Cells(i, 3)
            Select Case rng
               Case 0 To 17
                    age = "0 - 17"
               Case 18 To 35
                    age = "18 - 35"
               Case 36 To 45
                    age = "36 - 45"
               Case 46 To 55
                    age = "46 - 55"
               Case 56 To 60
                    age = "56 - 60"
               Case 61 To 65
                    age = "61 - 65"
               Case Is >= 66
                    age = "66"
            End Select
                Set cel = .Range("A1:X1").Find(what:=a, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                Set cel2 = .Range("A2:X2").Find(what:=b, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                Set cel3 = .Range("S2:X2").Find(what:=c, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                Set cel4 = .Range("A3:A9").Find(what:=age, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                Set cel5 = .Range("S3:S6").Find(what:=a, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
                     a1 = cel4.Row
                     b1 = cel2.Column
                     c1 = cel3.Column
                     d1 = cel.Column
                     e1 = cel5.Row
                        If .Cells(2, d1).Offset(, -1).Value = cel2 Then
                            ws2.Cells(i, 7).Value = .Cells(a1, d1).Offset(, -1).Value
                        End If
                        If .Cells(2, d1).Value = cel2 Then
                            ws2.Cells(i, 7).Value = .Cells(a1, d1).Value
                        End If
                        If .Cells(2, d1).Offset(, 1).Value = cel2 Then
                            ws2.Cells(i, 7).Value = .Cells(a1, d1).Offset(, 1).Value
                        End If
                        With ws2
                           .Cells(i, 8).Value = ws1.Cells(e1, c1).Value
                           .Cells(i, 9).FormulaR1C1 = "=SUM(RC[-2]*RC[-1]/100)"
                           .Cells(i, 10).FormulaR1C1 = "=SUM(RC[-3]+RC[-1])"
                        End With
         Next i
      End With
     Application.ScreenUpdating = 1
End Sub

hi hecgroups, as an option, formula only solution

G2:

=OFFSET('Calculation Data'!$A$1,VLOOKUP(C209,{0;2:18;3:36;4:46;5:56;6:61;7:66;8},2,1),VLOOKUP(B209,{"VIP";2:"A";6:"B";10:"C";14},2,0)+VLOOKUP(D209,{"Employee";0:"Spouse";1:"Child";2},2,0))

H2:

=HLOOKUP(IF(F2="OTHER",F2&"s",F2),'Calculation Data'!$S$2:$X$6,VLOOKUP(B2,{"VIP";2:"A";3:"B";4:"C";5},2,0),0)