Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Stuck on inserting long formula using VB

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,360
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Stuck on inserting long formula using VB

    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.

    What is it that I am doing wrong? Can you please help me out?

    Asad
    Last edited by asad; Apr 27th, 2016 at 10:58 PM.

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,418
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stuck on inserting long formula using VB

    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

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,360
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stuck on inserting long formula using VB

    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.

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,418
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stuck on inserting long formula using VB

    Great! I am glad you got it working.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •