Need VBA code

[nileshingley]

Hi,

Please provide the VBA code for the following formula.

=IF((OR($AB$7="NPI",$AB$7="NPI-Lite")),(IF(AL8="(C) Initiated","5%",IF(OR(AL8="(C) MRD Sign off",AL8="(C) L&R Sign off",AL8="(C) FTA Intiated"),"10%",IF(AL8="(D) SRD Intitiated","30%",IF(OR(AL8="(D) Requirements - HLA and ROM sign off",AL8="(D) SRD sign off",AL8="(D) P&P Arch. Initiated",AL8="(D) Finance Sign off",AL8="(R) Dev. Initiated"),"50%",IF(AL8="(R) Del. To UAT","60%",IF(OR(AL8="(R) UAT Initiated",AL8="(R) UAT Sign Off"),"90%",IF(OR(AL8="(R) Go Live",AL8="(F) CI Sign Off"),"95%",IF(AL8="(F) GA Sign off","100%",IF((OR($AB$7<>"NPI",$AB$7<>"NPI-Lite")),IF(AL8="(PCR) Requirement","10%",IF(AL8="(PCR) Grooming","20%",IF(AL8="(PCR) Solution","30%",IF(AL8="(PCR) Development","50%",IF(AL8="(PCR) SIT","70%",IF(AL8="(PCR) UAT","80%",IF(AL8="(PCR) Deployment","100%","0%"))))))))))))))))))

Thanks,
Nilesh Ingle

 

[shg]

Does that formula work? It seems to me it should be

=IF(OR($AB$7 = "NPI", $AB$7 = "NPI-Lite"),
   IF(   AL8 = "(C) Initiated", 5%,
   IF(OR(AL8 = {"(C) MRD Sign off","(C) L&R Sign off","(C) FTA Intiated"}), 10%,
   IF(   AL8 = "(D) SRD Intitiated", 30%,
   IF(OR(AL8 = {"(D) Requirements - HLA and ROM sign off","(D) SRD sign off","(D) P&P Arch. Initiated","(D) Finance Sign off","(R) Dev. Initiated"}), 50%,
   IF(   AL8 = "(R) Del. To UAT", 60%,
   IF(OR(AL8 = {"(R) UAT Initiated","(R) UAT Sign Off"}), 90%,
   IF(OR(AL8 = {"(R) Go Live","(F) CI Sign Off"}), 95%,
   IF(   AL8 = "(F) GA Sign off", 100%)))))))),
IF(AL8 = "(PCR) Requirement", 10%,
   IF(   AL8 = "(PCR) Grooming", 20%,
   IF(   AL8 = "(PCR) Solution", 30%,
   IF(   AL8 = "(PCR) Development", 50%,
   IF(   AL8 = "(PCR) SIT", 70%,
   IF(   AL8 = "(PCR) UAT",80%,
   IF(   AL8="(PCR) Deployment", 100%, 0%))))))))

.. because this

IF(OR($AB$7<>"NPI", $AB$7 <> "NPI-Lite")

is ALWAYS true.

 

[nileshingley]

Yes, you are right. I checked the formula was not getting thr desired result. Pls send the code

 

[shg]

And if that's true, I'd put the data in a table and use a simpler formula:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0​

[/td][td]B2: =IFERROR(VLOOKUP(AL8, IF(OR($AB$7 = {"NPI","NPI-Lite"}), $A$4:$B$19, $A$21:$B$27), FALSE), 0)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td](C) Initiated[/td][td]

5%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td](C) MRD Sign off[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td](C) L&R Sign off[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td](C) FTA Intiated[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td](D) SRD Intitiated[/td][td]

30%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td](D) Requirements - HLA and ROM sign off[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td](D) SRD sign off[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td](D) P&P Arch. Initiated[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td](D) Finance Sign off[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td](R) Dev. Initiated[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td](R) Del. To UAT[/td][td]

60%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td](R) UAT Initiated[/td][td]

90%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td](R) UAT Sign Off[/td][td]

90%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td](R) Go Live[/td][td]

95%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td](F) CI Sign Off[/td][td]

95%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td](F) GA Sign off[/td][td]

100%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td](PCR) Requirement[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td](PCR) Grooming[/td][td]

20%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td](PCR) Solution[/td][td]

30%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td](PCR) Development[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td](PCR) SIT[/td][td]

70%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td](PCR) UAT[/td][td]

80%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td](PCR) Deployment[/td][td]

100%​

[/td][td][/td][/tr]
[/table]

 

[spiralrain]

For the vba code, if you still need it, I think this would work for you. Just change the cell number at the very bottom from A1 to the cell that the original code was in.

Sub testing()

Dim AB7 As String
Dim AL8 As String
Dim Ret As String

AB7 = Range("AB7")
AL8 = Range("AL8")

If AB7 = "NPI" Or AB7 = "NPI-Lite" Then
Select Case AL8
Case "(C) Initiated"
Ret = "5%"
Case "(C) MRD Sign off"
Ret = "10%"
Case "(C) L&R Sign off"
Ret = "10%"
Case "(C) FTA Intiated"
Ret = "10%"
Case "(D) SRD Intitiated"
Ret = "30%"
Case "(D) Requirements - HLA and ROM sign off"
Ret = "50%"
Case "(D) SRD sign off"
Ret = "50%"
Case "(D) P&P Arch. Initiated"
Ret = "50%"
Case "(D) Finance Sign off"
Ret = "50%"
Case "(R) Dev. Initiated"
Ret = "50%"
Case "(R) Del. To UAT"
Ret = "60%"
Case "(R) UAT Initiated"
Ret "90%"
Case "(R) UAT Sign Off"
Ret = "90%"
Case "(R) Go Live"
Ret = "95%"
Case "(F) CI Sign Off"
Ret = "95%"
Case "(F) GA Sign off"
Ret = "100%"
End Select
Else
Select Case AL8
Case "(PCR) Requirement"
Ret = "10%"
Case "(PCR) Grooming"
Ret = "20%"
Case "(PCR) Solution"
Ret = "30%"
Case "(PCR) Development"
Ret = "50%"
Case "(PCR) SIT"
Ret = "70%"
Case "(PCR) UAT"
Ret = "80%"
Case "(PCR) Deployment"
Ret = "100%"
End Select
End If


Range("A1") = Ret


End Sub

 

[nileshingley]

Thank you!!