CODEWITHSUNDEEP
vbaexcel
Bogdan Ionică
Bogdan Ionică

Reputation: 157

Can VBA attempt to assign to a cell a formula that is too long?

I am on Windows, if that matters. This code seems to attempt to assign a formula that is too long:

ActiveSheet.ListObjects("SegmentValues_1").ListColumns("Change").DataBodyRange.Formula = "=IFERROR(IF(INDEX(SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[CODE_ZONE],MATCH([@Modules],SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[Modules],0))=0,""Last total was ZERO"",([@CODE_ZONE]-INDEX(SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[CODE_ZONE],MATCH([@Modules],SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[Modules],0)))/INDEX(SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[CODE_ZONE],MATCH([@Modules],SegmentValues_" & (ActiveWorkbook.Sheets.count - 2) & "[Modules],0)))),""Did not exist previously"")"

Is such a thing possible? If so, how to solve it?

Upvotes: 0

Views: 36

Answers (1)

Vityata
Vityata

Reputation: 43585

The long formulas work in Excel VBA quite well. Take a look at this 24 nested IF():

Sub TestMe()

    Range("A1").Formula = "=IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1," & _
            "IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1," & _
            "IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1," & _
            "IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1," & _
            "IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1,IF(1=1," & _
            "IF(1=1,2))))))))))))))))))))))))))))))))))))))))))))))"

End Sub

It is translated really well into Excel. Thus, take a good look at your formula, the problem should be there.

Upvotes: 1

Related Questions