Formula in vba

[ajitexcel]

Hello ALL,

i have uploaded sample file at http://www.box.com/s/fqjczrjav22gl7k9cs0k "SAMPLE2.XLSX".

I intend to write present code written in formula to be written in VBA, can any one help me with that.

I have formula in column (named Result) N6 onwards and (Remarks) O6 onwards. is there any way that instead of writting formula in cell i could get same result by writing code in VBA, so that if some one removes any row result does not get wrongly referenced.

The condition for results are,
1. Candidate should Qualify "Q" in Height bar (G)
2. Candidate should Qualify "Q" in Run (H)
3. Height :- if candidate is "ST" then min height to qualify is 162.5 cm and
for others min height is 170 cm (I)
4. Chest :- if candidate is "ST" then min chest is 76 cm and for others it is
80 cm. (J)
5. Expansion :- Min expansion required is 5 cm i.e. (J - I)

6. if columns G to M are empty then it is assumed candidate is absent "A".
7. If an ST candidate qualifies then remarks column has "Subject to Cert."
all this is being achived with the formula in column N & O.

Thanx in advance
formula used in result column (N)

Code:
=IF(AND(G6=0,H6=0,I6=0,J6=0,K6=0),"A",IF(H6="NQ","NQ",IF(I6="NQ","NQ",IF(AND(G6="ST",J6<162.5),"NQ",IF(AND(G6<>"ST",J6<170),"NQ",IF(AND(G6="ST",K6<76),"NQ",IF(AND(G6<>"ST",K6<80),"NQ",IF(AND(G6="ST",L6-K6<5),"NQ",IF(AND(G6<>"ST",L6-K6<5),"NQ","Q")))))))))& formula used in remarks column (O)

Code:
=IF(N6="A","",IF(H6="NQ","Height Bar",IF(I6="NQ","Run",IF(AND(G6="ST",J6<162.5),"Less Height",IF(AND(G6=" ",H6=" ",I6=" ",J6=" ",K6=" ")," ",IF(AND(G6<>"ST",J6<170),"Less Height",IF(AND(G6="ST",K6<76),"Less Chest",IF(AND(G6<>"ST",K6<80),"Less Chest",IF(AND(G6="ST",L6-K6<5),"Less Chest Expansion",IF(AND(G6<>"ST",L6-K6<5),"Less Chest Expansion",IF(AND(G6="ST",N6="Q"),"Subject to Cert."," ")))))))))))

I have posted this on http://www.mrexcel.com/forum/showthread.php?t=618066.

regards

aJIT

[ajitexcel]

Nobody in EXCELFORUM to solve the mystry ?????

[marreco]

Try..

Please Login or Register  to view this content.