Could we make an excel formula shorter with use UDF function?

[Saher Naji]

I have this formula in excel, which is very long:

and this is the BVA code, which is generated once I record a macro:

Sub tESTpRICE()
'
' tESTpRICE Macro
'

'
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-26]=1,RC[-1],IF(RC[-26]=2,RC[-1]+R[1]C[-1],IF(RC[-26]=3, RC[-1]+R[1]C[-1]+R[2]C[-1],IF(RC[-26]=4, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1],IF(RC[-26]=5, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1],IF(RC[-26]=6, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1],IF(RC[-26]=7, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1],IF(RC[-26]=8" & _
        "+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1],IF(RC[-26]=9, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1],IF(RC[-26]=10, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1]+R[9]C[-1],IF(RC[-26]=11, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[" & _
        "[8]C[-1]+R[9]C[-1]+R[10]C[-1],IF(RC[-26]=12, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1]+R[9]C[-1]+R[10]C[-1]+R[11]C[-1],IF(RC[-26]=13, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1]+R[9]C[-1]+R[10]C[-1]+R[11]C[-1]+R[12]C[-1],IF(RC[-26]=14, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-" & _
        "-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1]+R[9]C[-1]+R[10]C[-1]+R[11]C[-1]+R[12]C[-1]+R[13]C[-1],IF(RC[-26]=15, RC[-1]+R[1]C[-1]+R[2]C[-1]+R[3]C[-1]+R[4]C[-1]+R[5]C[-1]+R[6]C[-1]+R[7]C[-1]+R[8]C[-1]+R[9]C[-1]+R[10]C[-1]+R[11]C[-1]+R[12]C[-1]+R[13]C[-1]+R[14]C[-1])))))))))))))))"
    Range("AB2").Select
    Selection.AutoFill Destination:=Range("AB2:AB12459")
    Range("AB2:AB12459").Select
End Sub

Is it possible to create a UDF formula, because there are a problem with using this long formula

Thank you very much

 

[Fluff]

No need for a UDF, assuming that col B will always be 1 or greater you can use
=SUM(AA2:INDEX(AA2:AA100,B2))

 

[Saher Naji]

Thank you,
The new formula is working, and the Marco is working too

but do you thinks this error will affect?

 

[Fluff]

You're welcome & thanks for the feedback