# Stuck on inserting long formula using VB

##### Well-known Member
Hi Guys,

I am trying to insert this formula:
Code:
``{=IFERROR(PROPER(IF(B5="","",IF(AND(C\$1=E\$1,MID(B5,2,1)*1=1),INDEX(INDIRECT(F\$1&"B13:B193"),MATCH(B5,INDIRECT(F\$1&"AT13:AT193"),0)),INDEX(Express_Names,MATCH(B5,INDIRECT("Express!"&HLOOKUP(A\$1-C5,Express!\$D\$10:\$J\$11,2,0)&"13:"&HLOOKUP(A\$1-C5,Express!\$D\$10:\$J\$11,2,0)&"193"),0))))),"")}``
using this code:
Code:
``````Dim fp As Range, rng As Range, c As Range
Set fp = Range("F1")
Set fpl = Range("H1")
Set rng = Range("D5", "D14")
For Each c In rng
c.FormulaArray = "=iferror(proper(if(" & c.Offset(, 2).Address & "="""","""",If(and(" & Range("c1") & "=" & Range("E1") & ",MID(" _
& c.Offset(, 2).Address & ",2,1)*1=1),Index(" & fp & "B13:B193,Match(" & c.Offset(, 2).Address & "," & fp & "AT13:At193,0)),Index(" & Express_Names & ",Match(" _
& c.Offset(, 2).Address & "," & fpl & ",0))))),"""")"
Next c``````
It is giving me an error.

Last edited:

#### StephenCrump

##### Well-known Member
It's not immediately clear why you don't just use your first formula in the VBA, or whether the second formula matches the first?

But either way, you're running into the 255 character limit for .FormulaArray.

You can work around it by cutting your formula into smaller lengths.

See, for example: Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA

##### Well-known Member
Thanks a lot Stephen. I took your advice and did exactly what you said and it WORKED.
I just did the check in VB and if true then do the required else nothing.

Thanks again.

#### StephenCrump

##### Well-known Member
Great! I am glad you got it working.