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!
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