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:

	A​	B​	C​
2​		0​	B2: =IFERROR(VLOOKUP(AL8, IF(OR($AB$7 = {"NPI","NPI-Lite"}), $A$4:$B$19, $A$21:$B$27), FALSE), 0)
3​
4​	(C) Initiated	5%​
5​	(C) MRD Sign off	10%​
6​	(C) L&R Sign off	10%​
7​	(C) FTA Intiated	10%​
8​	(D) SRD Intitiated	30%​
9​	(D) Requirements - HLA and ROM sign off	50%​
10​	(D) SRD sign off	50%​
11​	(D) P&P Arch. Initiated	50%​
12​	(D) Finance Sign off	50%​
13​	(R) Dev. Initiated	50%​
14​	(R) Del. To UAT	60%​
15​	(R) UAT Initiated	90%​
16​	(R) UAT Sign Off	90%​
17​	(R) Go Live	95%​
18​	(F) CI Sign Off	95%​
19​	(F) GA Sign off	100%​
20​
21​	(PCR) Requirement	10%​
22​	(PCR) Grooming	20%​
23​	(PCR) Solution	30%​
24​	(PCR) Development	50%​
25​	(PCR) SIT	70%​
26​	(PCR) UAT	80%​
27​	(PCR) Deployment	100%​

 

[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!!