Long Array issue with VBA

snoh8r

New Member
Joined
Jan 30, 2018
Messages
22
Office Version
  1. 365
Platform
  1. Windows
It was one thing to write this formula and have success but getting it to VBA has been killing me. I Couldn't use Vlookup to return items as one lookup value may have several entries and several results on the opposing sheet. So I concocted the formula below. Then I tried to put it into my maco, and that is when the headache began. I did some research about splitting the code. I can get this to work, except if I put an "=" in front of the first formula line. Then I get the unable to define array (or whatever). I started digging into the idea that each little piece needs to be an actual formula on it's own. I got nowhere fast. Maybe I'm not even going the right way with this. Maybe my formula could be better (I don't see how), but I could really use some help.

Thanks!

Code:
Sub THisDoesNotWork() ' not my original title but ....
Dim FormulaP1 As String, FormulaP2 As String, FormulaP3 As String, FormulaP4 As String, FormulaP5 As String, _
FormulaP6 As String, FormulaP7 As String, FormulaP8 As String, FormulaP9 As String, FormulaP10 As String, _
FormulaP11 As String, FormulaP12 As String, FormulaP13 As String, FormulaP14 As String, FormulaP15 As String, _
FormulaP16 As String, FormulaP17 As String, FormulaP18 As String, FormulaP19 As String, FormulaP20 As String, _
FormulaP21 As String, FormulaP22 As String, FormulaP23 As String, FormulaP24 As String, FormulaP25 As String, _
FormulaP26 As String, FormulaP27 As String, FormulaP28 As String, FormulaP29 As String, FormulaP30 As String, _
FormulaP31 As String, FormulaP32 As String



FormulaP1 = "IF(COUNTIF(Prod!A:A,A2)=1,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),IF(COUNTIF(Prod!A:A,A2)=2,IF(ISERROR(INDEX(Prod!$A:$B,SMALL" & "P2"

FormulaP2 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2),IF(COUNTIF(Prod!A:A,A2)=3,IF(ISERROR&" & "P3"

FormulaP3 = _
"(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX" & "P4"

FormulaP4 = _
"(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2),IF(COUNTIF(Prod!A:A,A2)=4,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:" & "P5"

FormulaP5 = _
"$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:" & "P6"

FormulaP6 = _
"$4)),2),IF(COUNTIF(Prod!A:A,A2)=5,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2," & "P7"

FormulaP7 = _
"ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2," & "P8"

FormulaP8 = _
"ROW(Prod!$A:$B)),ROW($5:$5)),2),IF(COUNTIF(Prod!A:A,A2)=6,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B," & "P9"

FormulaP9 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B," & "P10"

FormulaP10 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2),IF(COUNTIF(Prod!A:A,A2)=7,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2" & "P11"

FormulaP11 = _
")),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR" & "P12"

FormulaP12 = _
"(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR" & "P13"

FormulaP13 = _
"(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2),IF(COUNTIF(Prod!A:A,A2)=8,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P14"

FormulaP14 = _
")),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P15"

FormulaP15 = _
")),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B))" & "P16"

FormulaP16 = _
",ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2),IF(COUNTIF(Prod!A:A,A2)=9,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$" & "P17"

FormulaP17 = _
"A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod" & "P18"

FormulaP18 = _
"!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(" & "P19"

FormulaP19 = _
"Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2),IF(COUNTIF(Prod!A:A,A2)=10,IF(ISERROR" & "P20"

FormulaP20 = _
"(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&" & "P21"

FormulaP21 = _
"INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)" & "P22"

FormulaP22 = _
"&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)" & "P23"

FormulaP23 = _
"&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2),IF(COUNTIF(Prod!A:A,A2)=11,IF(ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$" & "P24"

FormulaP24 = _
"A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P25"

FormulaP25 = _
")),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P26"

FormulaP26 = _
")),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B" & "P27"

FormulaP27 = _
")),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($11:$11)),2),IF(COUNTIF(Prod!A:A,A2)=12,IF(ISERROR(INDEX(Prod!$A:$B,SMALL" & "P28"

FormulaP28 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2)),"""",INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($1:$1)),2))&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($2:$2)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL" & "P29"

FormulaP29 = _
"(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($3:$3)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($4:$4)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($5:$5)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(" & "P30"

FormulaP30 = _
"IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($6:$6)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($7:$7)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($8:$8)),2)&CHAR(10)&INDEX(Prod!$A:$B," & "P31"

FormulaP31 = _
"SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($9:$9)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($10:$10)),2)&CHAR(10)&INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($11:$11)),2)&CHAR(10)&INDEX" & "P32"

FormulaP32 = _
"(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A2,ROW(Prod!$A:$B)),ROW($12:$12)),2),""""))))))))))))"

With ActiveSheet.Range("E2")
    
    .FormulaArray = FormulaP1
    .Replace "P2", FormulaP2
    .Replace "P3", FormulaP3
    .Replace "P4", FormulaP4
    .Replace "P5", FormulaP5
    .Replace "P6", FormulaP6
    .Replace "P7", FormulaP7
    .Replace "P8", FormulaP8
    .Replace "P9", FormulaP9
    .Replace "P10", FormulaP10
    .Replace "P11", FormulaP11
    .Replace "P12", FormulaP12
    .Replace "P13", FormulaP13
    .Replace "P14", FormulaP14
    .Replace "P15", FormulaP15
    .Replace "P16", FormulaP16
    .Replace "P17", FormulaP17
    .Replace "P18", FormulaP18
    .Replace "P19", FormulaP19
    .Replace "P20", FormulaP20
    .Replace "P21", FormulaP21
    .Replace "P22", FormulaP22
    .Replace "P23", FormulaP23
    .Replace "P24", FormulaP24
    .Replace "P25", FormulaP25
    .Replace "P26", FormulaP26
    .Replace "P27", FormulaP27
    .Replace "P28", FormulaP28
    .Replace "P29", FormulaP29
    .Replace "P30", FormulaP30
    .Replace "P31", FormulaP31
    .Replace "P32", FormulaP32
End With

End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I finally found a way to make this work. I realized that each "Index" was itself a formula. From there I built this:
(Tested on Cell A142 as it has 12 returned values. Also tested all others)

Code:
Sub ThisWorks()
Dim iVal As Integer
iVal = Application.WorksheetFunction.CountIf(Sheets("Prod").Range("A:A"), Sheets("7-9-18").Range("A142"))
AAAA = "ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)"
AAA = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($1:$1)),2)"
BBB = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($2:$2)),2)"
CCC = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($3:$3)),2)"
 
DDD = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($4:$4)),2)"
EEE = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($5:$5)),2)"
FFF = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($6:$6)),2)"
GGG = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($7:$7)),2)"
HHH = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($8:$8)),2)"
III = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($9:$9)),2)"
JJJ = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($10:$10)),2)"
LLL = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($11:$11)),2)"
MMM = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A142,ROW(Prod!$A:$B)),ROW($12:$12)),2)"
NNN = "&Char(10)&"
    If iVal = 1 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")"
    ElseIf iVal = 2 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB"
    ElseIf iVal = 3 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC"
    ElseIf iVal = 4 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD"
    ElseIf iVal = 5 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE"
    ElseIf iVal = 6 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF"
    ElseIf iVal = 7 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG"
    ElseIf iVal = 8 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH"
    ElseIf iVal = 9 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III"
    ElseIf iVal = 10 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ"
    ElseIf iVal = 11 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL"
    ElseIf iVal = 12 Then
        Ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & AAA & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM"
    End If
    
    MsgBox iVal
    With Range("E142")
        .FormulaArray = Ret1
        .Replace "AAA", AAA
        .Replace "BBB", BBB
        .Replace "CCC", CCC
        .Replace "DDD", DDD
        .Replace "EEE", EEE
        .Replace "FFF", FFF
        .Replace "GGG", GGG
        .Replace "HHH", HHH
        .Replace "III", III
        .Replace "JJJ", JJJ
        .Replace "LLL", LLL
        .Replace "MMM", MMM
    End With
 
Upvote 0
This was removed from the final copy.
Code:
NNN = "&Char(10)&"
 
Upvote 0
And finally, I realized I had to account for change of cell A2 to A3, then A4 and so on. I came up with this:

Code:
Sub ProductCIArrays()
Dim rngAddress1 As Range, rngAddress5 As Range, iVal As Integer
Application.ScreenUpdating = False
Set rngAddress1 = Sheets(2).Range("A1:ZZ1").Find("PBI ID")
Set rngAddress5 = Sheets(2).Range("A1:ZZ1").Find("Product CI")
rngAddress1.Offset(1, 0).Select
i = 1
    
    Do While Not ActiveCell = ""
    
    AAAA = "ISERROR(INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=$A" & 1 + i & ",ROW(Prod!$A:$B)"
    
    AAA = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($1:$1)),2)"
    
    BBB = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($2:$2)),2)"
    
    CCC = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($3:$3)),2)"
     
    DDD = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($4:$4)),2)"
    
    EEE = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($5:$5)),2)"
    
    FFF = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($6:$6)),2)"
    
    GGG = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($7:$7)),2)"
    
    HHH = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($8:$8)),2)"
    
    III = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($9:$9)),2)"
    
    JJJ = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($10:$10)),2)"
    
    LLL = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($11:$11)),2)"
    
    MMM = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($12:$12)),2)"
    
    NNN = "INDEX(Prod!$A:$B,SMALL(IF(Prod!$A:$B=A" & 1 + i & ",ROW(Prod!$A:$B)),ROW($13:$13)),2)"
    
    iVal = Application.WorksheetFunction.CountIf(Sheets("Prod").Range("A:A"), ActiveCell)
    
        If iVal = 0 Then
            ret1 = ""
        ElseIf iVal = 1 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")"
        ElseIf iVal = 2 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB"
        ElseIf iVal = 3 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC"
        ElseIf iVal = 4 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD"
        ElseIf iVal = 5 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE"
        ElseIf iVal = 6 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF"
        ElseIf iVal = 7 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG"
        ElseIf iVal = 8 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH"
        ElseIf iVal = 9 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III"
        ElseIf iVal = 10 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ"
        ElseIf iVal = 11 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL"
        ElseIf iVal = 12 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM"
        ElseIf iVal = 13 Then
            ret1 = "=IF(" & AAAA & "),ROW($1:$1)),2)),""""," & "AAA" & ")&Char(10)&" & "BBB" & "&Char(10)&" & "CCC" & "&Char(10)&" & "DDD" & "&Char(10)&" & "EEE" & "&Char(10)&" & "FFF" & "&Char(10)&" & "GGG" & "&Char(10)&" & "HHH" & "&Char(10)&" & "III" & "&Char(10)&" & "JJJ" & "&Char(10)&" & "LLL" & "&Char(10)&" & "MMM" & "&Char(10)&" & "NNN"
        End If
        
        With rngAddress5.Offset(i, 0)
            .FormulaArray = ret1
            .Replace "AAA", AAA
            .Replace "BBB", BBB
            .Replace "CCC", CCC
            .Replace "DDD", DDD
            .Replace "EEE", EEE
            .Replace "FFF", FFF
            .Replace "GGG", GGG
            .Replace "HHH", HHH
            .Replace "III", III
            .Replace "JJJ", JJJ
            .Replace "LLL", LLL
            .Replace "MMM", MMM
            .Replace "NNN", NNN
            End With
        
        rngAddress1.Offset(1 + i, 0).Activate
        
        If ActiveCell = "" Then
            i = 1
        Else
            i = i + 1
        End If
        
    Loop
        
        Columns("E:E").Select
    
        With Selection
            .VerticalAlignment = xlBottom
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        
Application.ScreenUpdating = True
        
End Sub

This evaluates 580 rows and takes about 8 minutes. I would like to get it faster. If anyone is following this thread and has any ideas, I would really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,418
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top