CODEWITHSUNDEEP
vba
David Levy
David Levy

Reputation: 21

Run time error 1004 placing formula in cell

I have created a formula that works in a spreadsheet but not in VBA. What I have pasted here is only a subset of the entire formula; in total it exceeds 3,000 characters

If I run this, it works

LastRow = Range("a" & Rows.Count).End(xlUp).Row
Range("cI2:cI" & LastRow).Formula = "=CONCATENATE(CONCATENATE(MID(""00"",1,2-LEN(A2)),A2))"

If I run this, I get the run time error

LastRow = Range("a" & Rows.Count).End(xlUp).Row
Range("cI2:cI" & LastRow).Formula = "=CONCATENATE(CONCATENATE(MID(""00"",1,2-LEN(A2)),A2)), " & _
"CONCATENATE(MID(""00000000"",1,8-LEN(B2)),B2),""    "", CONCATENATE(C2,MID(""            "",1,12-LEN(C2))),CONCATENATE(MID(""00"",1,2-LEN(D2)),D2) "

Upvotes: 2

Views: 112

Answers (1)

Comintern
Comintern

Reputation: 22185

Without seeing the rest of the formula, I would suspect that it exceeds some of the internal limits Excel has for function. Possibilities that come to mind are:

Arguments in a function: 255

Nested levels of functions: 64

Length of formula contents: 8,192 characters

You can find more information on the Office support page here.

That said, I'm curious why you would need to build a 3000 character formula to place in the cell. This is what VBA is for - converting the formula into a user defined function is likely going to be the solution.

Upvotes: 1

Related Questions