Big formula through VBA

[abidraza]

Range("F5:F1004").Formula = "=IF('Student Data'!E2<'Gross Fee Monthly'!$F$2,IF('Student Data'!H2=Setups!$C$2,IF(Setups!$F$2=Setups!$A$2,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$3:$E$17,2,FALSE),IF(Setups!$F$2=Setups!$A$3,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$20:$E$34,2,flase),IF(Setups!$F$2=Setups!$A$4,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$37:$E$51,2,FALSE),IF(Setups!$F$2=Setups!$A$5,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$54:$E$68,2,FALSE),IF(Setups!$F$2=Setups!$A$6,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$71:$E$85,2,FALSE),IF(Setups!$F$2=Setups!$A$7,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$88:$E$102,2,FALSE),IF(Setups!$F$2=Setups!$A$8,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$105:$E$119,2,FALSE),IF(Setups!$F$2=Setups!$A$9,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$122:$E$136,2,FALSE),IF(Setups!$F$2=Setups!$A$10,VLOOKUP('Fee structure'!$A$139:$E$153,2,FALSE),IF(Setups!$F$2=Setups!$A$11,_"
&"VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$155:$E$169,2,FALSE),))))))_"
& )))),IF('Student Data'!I2<='Gross Fee Monthly'!$F$2,0,IF(Setups!$F$2=Setups!$A$2,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$3:$E$17,2,FALSE),IF(Setups!$F$2=Setups!$A$3,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$20:$E$34,2,flase),IF(Setups!$F$2=Setups!$A$4,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$37:$E$51,2,FALSE),IF(Setups!$F$2=Setups!$A$5,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$54:$E$68,2,FALSE),IF(Setups!$F$2=Setups!$A$6,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$71:$E$85,2,FALSE),IF(Setups!$F$2=Setups!$A$7,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$88:$E$102,2,FALSE),IF(Setups!$F$2=Setups!$A$8,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$105:$E$119,2,FALSE),IF(Setups!$F$2=Setups!$A$9,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$122:$E$136,2,FALSE),IF(Setups!$F$2=Setups!$A$10,VLOOKUP('Fee structure'!$A$139:$E$153,2,FALSE),IF(Setups!$F$2=Setups!$A$11,VLOOKUP('Gross Fee Monthly'!D5,'Fee structure'!$A$155:$E$169,2,FALSE),)))))))))))))



How to put this formula though VBA

 

[Andrew Poulsom]

What version of Excel do you have? Have you tried removing the line continuations?

 

[abidraza]

Excel 2007

 

[Andrew Poulsom]

You're going to have to shorten your formula. Try:

Sub Test()
    Dim Formula As String
    Formula = "=IF('Student Data'!E2<'Gross Fee Monthly'!$F$2,"
    Formula = Formula & "IF('Student Data'!H2=Setups!$C$2,"
    Formula = Formula & "VLOOKUP('Gross Fee Monthly'!D5,OFFSET('Fee structure'!$A$3:$E$170,(MATCH(Setups!$F$2,Setups!$A$2:$A$11,FALSE)-1)*17,0,15),2,FALSE)),"
    Formula = Formula & "IF('Student Data'!I2<='Gross Fee Monthly'!$F$2,0,"
    Formula = Formula & "VLOOKUP('Gross Fee Monthly'!D5,OFFSET('Fee structure'!$A$3:$E$170,(MATCH(Setups!$F$2,Setups!$A$2:$A$11,FALSE)-1)*17,0,15),2,FALSE)))"
    Range("F5:F1004").Formula = Formula
End Sub