Trim a code for Excel VBA

Question

I'm trying to enter this in as a macro, but I'm having trouble because the formula array needs to be less than 255 characters. I just want to have my macro fill all cells in my table6 with the array below. Any suggestions?

VBA:

Range("H31").Select
Selection.FormulaArray = _
    "=IFERROR(IF(IF([@[Generation Planted]]<>""F2"",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]=""F2"",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""""))=0,"""",IF([@[Generation Planted]]<>""F2"",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table" & _
    "])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]=""F2"",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""""))),"""")"
Selection.AutoFill Destination:=Range("Table6[Selection]"), Type:= _
    xlFillDefault
Range("Table6[Selection]").Select

The formula I want to use as an array in H31:H2043 (aka Table6[Selection]):

=IFERROR(IF(IF([@[Generation Planted]]<>"F2",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]="F2",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""))=0,"",IF([@[Generation Planted]]<>"F2",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]="F2",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""))),"")

The problem is the double formulas; Put this in another column that can be hidden; `=IFERROR(IF([@[Generation Planted]]<>""F2"",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3)),0)` then In H put a small if; `IF(Z31=0,"""",Z31)` Where Z is where you put the long formula. — Scott Craner, Mar 11 '16 at 21:08
If you do not want to do it that way then put the long formula in H as you want and put a custom number format of `[<>0]0;;;` this will turn any 0 to an empty cell visually. — Scott Craner, Mar 11 '16 at 21:15

score 0 · Answer 1 · answered Mar 11 '16 at 20:58

This is for filling the range "H31:H2043" with your formula:

Range("H3:H2043").Formula = "=IFERROR(IF(IF([@[Generation Planted]]<>"F2",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]="F2",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""))=0,"",IF([@[Generation Planted]]<>"F2",INDEX(Table45,MATCH(1,([@[Trait(s)]]=Table45[TRAIT])*([@[Embryo/Seed]]=Table45[Input_type]),0),3),IF([@[Generation Planted]]="F2",INDEX(Table46,MATCH(1,([@[Trait(s)]]=Table46[TRAIT])*([@[Embryo/Seed]]=Table46[Input_type]),0),3),""))),"")

"

score 0 · Answer 2 · answered Mar 11 '16 at 21:47

You can enter a very large array formula into a cell with VBA by using SendKeys (if your version of Windows/Excel supports SendKeys). Here is a very simple example:

Sub dural()
    Dim BigString As String

    BigString = "=A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1 & A1"
    MsgBox Len(BigString)

    Range("B8").Formula = BigString

    Range("B8").Select
    Application.SendKeys "{F2}"
    Application.SendKeys "^+{ENTER}"
End Sub

The trick is to first enter the formula normally and then apply Ctrl + Shift + Enter

score -1 · Answer 3 · answered Mar 11 '16 at 22:30

-1

So I just made a new column in the table with the array in each line in column r, then filled range h equal to r. dee dee dee.

answered Mar 11 '16 at 22:30

bwatson30

263
1
3
12

Trim a code for Excel VBA

3 Answers3