Break formula entry onto two lines in VBA.

Hi ExcelForum,

I have a macro that uses the loop below, but the formula highlighted in red is too long to be incorporated within such a code (Exceeds character limit). I was hoping someone on here could show me how to break the formula down to allow it to be applied to the cells selected by the loop?

Formula:

Please Login or Register  to view this content.

Thanks,

Fraser.

Hey Fraser

Try something like this :

form1 = "=MATCH(1,INDEX((R2C1:R30000C1=RC[-24])*(R2C4:R30000C4=RC[-21])*(R2C7:R30000C7=RC[-18])*(R2C9:R30000C9=RC[-16]),0,1),0)"
form2 = "=IF(RC[-2]="""",INDEX(R2C6:R30000C6,MATCH(MIN(IF((ABS((((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15])))))<>0,ABS(((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15]))))),((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15])),0)),""-"")"

For Row = 2 To 10000

If Cells(Row, 22).Value = Sheets("Control").Cells(3, 2).Value Then

Cells(Row, 25).Select

Selection.FormulaArray = form1

Cells(Row, 26).Select 'provides formula only to cells that need it.

Selection.FormulaArray = form2

Else: Cells(Row, 26).Value = "No Calc Required"

End If

Next Row

Regards

Geert

Fraser

The multiple lines were created by copying my code in the webinterface

To combine two lines you have to use & _ e.g.

form2 = "=IF(RC[-2]="""",INDEX(R2C6:R30000C6,MATCH(MIN(IF((ABS((((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15])))))<>0," & _ "ABS(((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15]))))),((R2C25:R30000C25)=(RC[-1]))*(ABS(R2C23:R30000C23-RC[-15])),0)),""-"")"

Regards

Geert

Geert,

My apologies, I had left off the '&' and simply used _. My mistake! Thanks for your help.

Fraser.

Hi Again,

This still does not solve the problem, VBA is still telling me that the characters are too long, even if i define the formula as you suggested? Am I still doing/no doing something silly?

Thanks again,

Fraser.

Fraser

I have tested it in excel 2010 and it works but excel 2007 allows less nested functions so that can be the problem.
Maybe splitting the formula and putting the first result in an hidden column could do the trick

Geert