# VBA formula too large

I think that I have a formula too large for VBA, I can't find any errors, but it is red.
Seems that I may need another solution, if there a way to split up and concatenate maybe?

``````Sub Calc()
Dim lngLastRowD As Long

With Sheets("Detail")
lngLastRowD = Sheets("Detail").Range("BI80000").End(xlUp).Row
.Range("BJ2:BJ" & lngLastRowD).Formula = "=IF(OR(\$F2={""FO"",""PO"",""SO"",""E2AM"",""E2"",""ESP""}),VLOOKUP(\$BG2,'Dom Ex'!\$A\$3:\$AP\$2000,40,FALSE), _
IF(OR(\$F2={""GR"",""HD"",""PRP""}),VLOOKUP(\$BG2,Ground!\$A\$3:\$AP\$2000,40,FALSE),IF(OR(\$F2={""F1"",""F2"",""F3""}), _
VLOOKUP(151,INDIRECT(""DOM_""&\$F2&""_MIN""),Detail!\$BD2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Letter"",\$AT2=""PR"",\$AV2=""EXPORT""),_
VLOOKUP(1,IP_EX_PR_L,2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Box"",\$AT2=""PR"",\$AV2=""EXPORT""), _
VLOOKUP(1,IP_EX_PR,2,FALSE),IF(AND(\$F2=""IE"",\$AT2=""PR"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_PR,2,FALSE), _
IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Letter""),VLOOKUP(1,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2="IP",\$AV2=""EXPORT"",\$BB2=""Pak""), _
VLOOKUP(2,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Box""),VLOOKUP(3,IP_EX_MIN,\$BD2,FALSE), _
IF(AND(\$F2=""IE"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Letter""), _
VLOOKUP(1,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2="IMPORT",\$BB2="Pak"),VLOOKUP(2,IP_IMP_MIN,\$BD2,FALSE), _
IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Box""),VLOOKUP(3,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IE"",\$AV2 _
=""IMPORT""),VLOOKUP(1,IE_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IPF"",\$AT2=""PR"",\$AV2=""EXPORT""), _
VLOOKUP(1,IP_EX_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IEF"", \$AT2="PR",\$AV2="EXPORT"),VLOOKUP(1,IE_EX_PR_FR_MIN,2,FALSE), _
IF(AND(\$F2=""IPF"", \$AT2=""PR"",\$AV2=""IMPORT""),VLOOKUP(1,IP_IMP_PR_FR_MIN,2,FALSE), IF(AND(\$F2=""IEF"",\$AT2=""PR"",\$AV2=""IMPORT""), _
VLOOKUP(1,IE_IMP_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IPF"",\$AV2=""EXPORT""), _
VLOOKUP(151,IPF_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IEF"",\$AV2=""EXPORT""),VLOOKUP(151,IEF_EX_MIN,\$BD2,FALSE), _
IF(AND(\$F2="IPF",\$AV2="IMPORT"),VLOOKUP(151,IPF_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2="IEF",\$AV2="IMPORT"), _
VLOOKUP(151,IEF_IMP_MIN,\$BD2,FALSE),""THEN""))))))))))))))))))))))"

End With

End Sub
``````
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi

Can you try this I just changed a few  ""
``````"=IF(OR(\$F2={""FO"",""PO"",""SO"",""E2AM"",""E2"",""ESP""}),VLOOKUP(\$BG2,'Dom Ex'!\$A\$3:\$AP\$2000,40,FALSE), _
IF(OR(\$F2={""GR"",""HD"",""PRP""}),VLOOKUP(\$BG2,Ground!\$A\$3:\$AP\$2000,40,FALSE),IF(OR(\$F2={""F1"",""F2"",""F3""}), _
VLOOKUP(151,INDIRECT(""DOM_""&\$F2&""_MIN""),Detail!\$BD2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Letter"",\$AT2=""PR"",\$AV2=""EXPORT""),_
VLOOKUP(1,IP_EX_PR_L,2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Box"",\$AT2=""PR"",\$AV2=""EXPORT""), _
VLOOKUP(1,IP_EX_PR,2,FALSE),IF(AND(\$F2=""IE"",\$AT2=""PR"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_PR,2,FALSE), _
IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Letter""),VLOOKUP(1,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Pak""), _
VLOOKUP(2,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Box""),VLOOKUP(3,IP_EX_MIN,\$BD2,FALSE), _
IF(AND(\$F2=""IE"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Letter""), _
VLOOKUP(1,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Pak""),VLOOKUP(2,IP_IMP_MIN,\$BD2,FALSE), _
IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Box""),VLOOKUP(3,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IE"",\$AV2 _
=""IMPORT""),VLOOKUP(1,IE_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IPF"",\$AT2=""PR"",\$AV2=""EXPORT""), _
VLOOKUP(1,IP_EX_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IEF"", \$AT2=""PR"",\$AV2="EXPORT"),VLOOKUP(1,IE_EX_PR_FR_MIN,2,FALSE), _
IF(AND(\$F2=""IPF"", \$AT2=""PR"",\$AV2=""IMPORT""),VLOOKUP(1,IP_IMP_PR_FR_MIN,2,FALSE), IF(AND(\$F2=""IEF"",\$AT2=""PR"",\$AV2=""IMPORT""), _
VLOOKUP(1,IE_IMP_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IPF"",\$AV2=""EXPORT""), _
VLOOKUP(151,IPF_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IEF"",\$AV2=""EXPORT""),VLOOKUP(151,IEF_EX_MIN,\$BD2,FALSE), _
IF(AND(\$F2=""IPF"",\$AV2=""IMPORT""),VLOOKUP(151,IPF_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IEF"",\$AV2=""IMPORT""), _
VLOOKUP(151,IEF_IMP_MIN,\$BD2,FALSE),""THEN""))))))))))))))))))))))"
``````
Author Commented:
@Kanti - Thanks - unfortunately still red. Not sure what else to try...
Commented:
Hi

Can you attach the file if the data is not sensitive?
Software EngineerCommented:
The string concatenation should end on each line with: " & _

This gives the following code:
``````.Range("BJ2:BJ" & lngLastRowD).Formula = "=IF(OR(\$F2={""FO"",""PO"",""SO"",""E2AM"",""E2"",""ESP""}),VLOOKUP(\$BG2,'Dom Ex'!\$A\$3:\$AP\$2000,40,FALSE)," & _
"IF(OR(\$F2={""GR"",""HD"",""PRP""}),VLOOKUP(\$BG2,Ground!\$A\$3:\$AP\$2000,40,FALSE),IF(OR(\$F2={""F1"",""F2"",""F3""}), " & _
"VLOOKUP(151,INDIRECT(""DOM_""&\$F2&""_MIN""),Detail!\$BD2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Letter"",\$AT2=""PR"",\$AV2=""EXPORT"")," & _
"VLOOKUP(1,IP_EX_PR_L,2,FALSE),IF(AND(\$F2=""IP"",\$BB2=""Box"",\$AT2=""PR"",\$AV2=""EXPORT""), " & _
"VLOOKUP(1,IP_EX_PR,2,FALSE),IF(AND(\$F2=""IE"",\$AT2=""PR"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_PR,2,FALSE), " & _
"IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Letter""),VLOOKUP(1,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Pak""), " & _
"VLOOKUP(2,IP_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""EXPORT"",\$BB2=""Box""),VLOOKUP(3,IP_EX_MIN,\$BD2,FALSE), " & _
"IF(AND(\$F2=""IE"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Letter""), " & _
"VLOOKUP(1,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Pak""),VLOOKUP(2,IP_IMP_MIN,\$BD2,FALSE), " & _
"IF(AND(\$F2=""IP"",\$AV2=""IMPORT"",\$BB2=""Box""),VLOOKUP(3,IP_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IE"",\$AV2 " & _
"=""IMPORT""),VLOOKUP(1,IE_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IPF"",\$AT2=""PR"",\$AV2=""EXPORT""), " & _
"VLOOKUP(1,IP_EX_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IEF"", \$AT2=""PR"",\$AV2=""EXPORT""),VLOOKUP(1,IE_EX_PR_FR_MIN,2,FALSE), " & _
"IF(AND(\$F2=""IPF"", \$AT2=""PR"",\$AV2=""IMPORT""),VLOOKUP(1,IP_IMP_PR_FR_MIN,2,FALSE), IF(AND(\$F2=""IEF"",\$AT2=""PR"",\$AV2=""IMPORT""), " & _
"VLOOKUP(1,IE_IMP_PR_FR_MIN,2,FALSE),IF(AND(\$F2=""IPF"",\$AV2=""EXPORT""), " & _
"VLOOKUP(151,IPF_EX_MIN,\$BD2,FALSE),IF(AND(\$F2=""IEF"",\$AV2=""EXPORT""),VLOOKUP(151,IEF_EX_MIN,\$BD2,FALSE), " & _
"IF(AND(\$F2=""IPF"",\$AV2=""IMPORT""),VLOOKUP(151,IPF_IMP_MIN,\$BD2,FALSE),IF(AND(\$F2=""IEF"",\$AV2=""IMPORT""), " & _
"VLOOKUP(151,IEF_IMP_MIN,\$BD2,FALSE),""THEN""))))))))))))))))))))))"
``````

Experts Exchange Solution brought to you by