vba to replicate formula- concatenate IF

[cadillacben]

Hello,

I have a large formula that I'd like to get in a macro, but it's too large. I've added the formula below, but basically its saying if two cells are not blank concatenate them with an & symbol. It just looks at a lot of cells, so it got big. Any help would be greatly appreciated.

=IF( AND( L2<>"", AB2<>"" ), CONCATENATE(L2," & ",AB2), "")& IF( AND( L2<>"", AC2<>"" ), CONCATENATE(", ", L2," & ",AC2), "")
& IF( AND( L2<>"", AD2<>"" ), CONCATENATE(", ", L2," & ",AD2), "")
& IF( AND( L2<>"", AE2<>"" ), CONCATENATE(", ", L2," & ",AE2), "")
& IF( AND( L2<>"", AF2<>"" ), CONCATENATE(", ", L2," & ",AF2), "")
& IF( AND( L2<>"", AG2<>"" ), CONCATENATE(", ", L2," & ",AG2), "")
& IF( AND( L2<>"", AH2<>"" ), CONCATENATE(", ", L2," & ",AH2), "")
& IF( AND( L2<>"", AI2<>"" ), CONCATENATE(", ", L2," & ",AI2), "")
& IF( AND( L2<>"", AJ2<>"" ), CONCATENATE(", ", L2," & ",AJ2), "")
& IF( AND( L2<>"", AK2<>"" ), CONCATENATE(", ", L2," & ",AK2), "")
& IF( AND( L2<>"", AL2<>"" ), CONCATENATE(", ", L2," & ",AL2), "")
& IF( AND( L2<>"", AM2<>"" ), CONCATENATE(", ", L2," & ",AM2), "")
& IF( AND( L2<>"", AN2<>"" ), CONCATENATE(", ", L2," & ",AN2), "")
& IF( AND( L2<>"", AO2<>"" ), CONCATENATE(", ", L2," & ",AO2), "")
& IF( AND( L2<>"", AP2<>"" ), CONCATENATE(", ", L2," & ",AP2), "")
& IF( AND( L2<>"", AQ2<>"" ), CONCATENATE(", ", L2," & ",AQ2), "")
& IF( AND( M2<>"", AB2<>"" ), CONCATENATE(", ", M2," & ",AB2), "")
& IF( AND( M2<>"", AC2<>"" ), CONCATENATE(", ", M2," & ",AC2), "")
& IF( AND( M2<>"", AD2<>"" ), CONCATENATE(", ", M2," & ",AD2), "")
& IF( AND( M2<>"", AE2<>"" ), CONCATENATE(", ", M2," & ",AE2), "")
& IF( AND( M2<>"", AF2<>"" ), CONCATENATE(", ", M2," & ",AF2), "")
& IF( AND( M2<>"", AG2<>"" ), CONCATENATE(", ", M2," & ",AG2), "")
& IF( AND( M2<>"", AH2<>"" ), CONCATENATE(", ", M2," & ",AH2), "")
& IF( AND( M2<>"", AI2<>"" ), CONCATENATE(", ", M2," & ",AI2), "")
& IF( AND( M2<>"", AJ2<>"" ), CONCATENATE(", ", M2," & ",AJ2), "")
& IF( AND( M2<>"", AK2<>"" ), CONCATENATE(", ", M2," & ",AK2), "")
& IF( AND( M2<>"", AL2<>"" ), CONCATENATE(", ", M2," & ",AL2), "")
& IF( AND( M2<>"", AM2<>"" ), CONCATENATE(", ", M2," & ",AM2), "")
& IF( AND( M2<>"", AN2<>"" ), CONCATENATE(", ", M2," & ",AN2), "")
& IF( AND( M2<>"", AO2<>"" ), CONCATENATE(", ", M2," & ",AO2), "")
& IF( AND( M2<>"", AP2<>"" ), CONCATENATE(", ", M2," & ",AP2), "")
& IF( AND( M2<>"", AQ2<>"" ), CONCATENATE(", ", M2," & ",AQ2), "")
& IF( AND( N2<>"", AB2<>"" ), CONCATENATE(", ", N2," & ",AB2), "")
& IF( AND( N2<>"", AC2<>"" ), CONCATENATE(", ", N2," & ",AC2), "")
& IF( AND( N2<>"", AD2<>"" ), CONCATENATE(", ", N2," & ",AD2), "")
& IF( AND( N2<>"", AE2<>"" ), CONCATENATE(", ", N2," & ",AE2), "")
& IF( AND( N2<>"", AF2<>"" ), CONCATENATE(", ", N2," & ",AF2), "")
& IF( AND( N2<>"", AG2<>"" ), CONCATENATE(", ", N2," & ",AG2), "")
& IF( AND( N2<>"", AH2<>"" ), CONCATENATE(", ", N2," & ",AH2), "")
& IF( AND( N2<>"", AI2<>"" ), CONCATENATE(", ", N2," & ",AI2), "")
& IF( AND( N2<>"", AJ2<>"" ), CONCATENATE(", ", N2," & ",AJ2), "")
& IF( AND( N2<>"", AK2<>"" ), CONCATENATE(", ", N2," & ",AK2), "")
& IF( AND( N2<>"", AL2<>"" ), CONCATENATE(", ", N2," & ",AL2), "")
& IF( AND( N2<>"", AM2<>"" ), CONCATENATE(", ", N2," & ",AM2), "")
& IF( AND( N2<>"", AN2<>"" ), CONCATENATE(", ", N2," & ",AN2), "")
& IF( AND( N2<>"", AO2<>"" ), CONCATENATE(", ", N2," & ",AO2), "")
& IF( AND( N2<>"", AP2<>"" ), CONCATENATE(", ", N2," & ",AP2), "")
& IF( AND( N2<>"", AQ2<>"" ), CONCATENATE(", ", N2," & ",AQ2), "")
& IF( AND( O2<>"", AB2<>"" ), CONCATENATE(", ", O2," & ",AB2), "")
& IF( AND( O2<>"", AC2<>"" ), CONCATENATE(", ", O2," & ",AC2), "")
& IF( AND( O2<>"", AD2<>"" ), CONCATENATE(", ", O2," & ",AD2), "")
& IF( AND( O2<>"", AE2<>"" ), CONCATENATE(", ", O2," & ",AE2), "")
& IF( AND( O2<>"", AF2<>"" ), CONCATENATE(", ", O2," & ",AF2), "")
& IF( AND( O2<>"", AG2<>"" ), CONCATENATE(", ", O2," & ",AG2), "")
& IF( AND( O2<>"", AH2<>"" ), CONCATENATE(", ", O2," & ",AH2), "")
& IF( AND( O2<>"", AI2<>"" ), CONCATENATE(", ", O2," & ",AI2), "")
& IF( AND( O2<>"", AJ2<>"" ), CONCATENATE(", ", O2," & ",AJ2), "")
& IF( AND( O2<>"", AK2<>"" ), CONCATENATE(", ", O2," & ",AK2), "")
& IF( AND( O2<>"", AL2<>"" ), CONCATENATE(", ", O2," & ",AL2), "")
& IF( AND( O2<>"", AM2<>"" ), CONCATENATE(", ", O2," & ",AM2), "")
& IF( AND( O2<>"", AN2<>"" ), CONCATENATE(", ", O2," & ",AN2), "")
& IF( AND( O2<>"", AO2<>"" ), CONCATENATE(", ", O2," & ",AO2), "")
& IF( AND( O2<>"", AP2<>"" ), CONCATENATE(", ", O2," & ",AP2), "")
& IF( AND( O2<>"", AQ2<>"" ), CONCATENATE(", ", O2," & ",AQ2), "")
& IF( AND( P2<>"", AB2<>"" ), CONCATENATE(", ", P2," & ",AB2), "")
& IF( AND( P2<>"", AC2<>"" ), CONCATENATE(", ", P2," & ",AC2), "")
& IF( AND( P2<>"", AD2<>"" ), CONCATENATE(", ", P2," & ",AD2), "")
& IF( AND( P2<>"", AE2<>"" ), CONCATENATE(", ", P2," & ",AE2), "")
& IF( AND( P2<>"", AF2<>"" ), CONCATENATE(", ", P2," & ",AF2), "")
& IF( AND( P2<>"", AG2<>"" ), CONCATENATE(", ", P2," & ",AG2), "")
& IF( AND( P2<>"", AH2<>"" ), CONCATENATE(", ", P2," & ",AH2), "")
& IF( AND( P2<>"", AI2<>"" ), CONCATENATE(", ", P2," & ",AI2), "")
& IF( AND( P2<>"", AJ2<>"" ), CONCATENATE(", ", P2," & ",AJ2), "")
& IF( AND( P2<>"", AK2<>"" ), CONCATENATE(", ", P2," & ",AK2), "")
& IF( AND( P2<>"", AL2<>"" ), CONCATENATE(", ", P2," & ",AL2), "")
& IF( AND( P2<>"", AM2<>"" ), CONCATENATE(", ", P2," & ",AM2), "")
& IF( AND( P2<>"", AN2<>"" ), CONCATENATE(", ", P2," & ",AN2), "")
& IF( AND( P2<>"", AO2<>"" ), CONCATENATE(", ", P2," & ",AO2), "")
& IF( AND( P2<>"", AP2<>"" ), CONCATENATE(", ", P2," & ",AP2), "")
& IF( AND( P2<>"", AQ2<>"" ), CONCATENATE(", ", P2," & ",AQ2), "")
& IF( AND( Q2<>"", AB2<>"" ), CONCATENATE(", ", Q2," & ",AB2), "")
& IF( AND( Q2<>"", AC2<>"" ), CONCATENATE(", ", Q2," & ",AC2), "")
& IF( AND( Q2<>"", AD2<>"" ), CONCATENATE(", ", Q2," & ",AD2), "")
& IF( AND( Q2<>"", AE2<>"" ), CONCATENATE(", ", Q2," & ",AE2), "")
& IF( AND( Q2<>"", AF2<>"" ), CONCATENATE(", ", Q2," & ",AF2), "")
& IF( AND( Q2<>"", AG2<>"" ), CONCATENATE(", ", Q2," & ",AG2), "")
& IF( AND( Q2<>"", AH2<>"" ), CONCATENATE(", ", Q2," & ",AH2), "")
& IF( AND( Q2<>"", AI2<>"" ), CONCATENATE(", ", Q2," & ",AI2), "")
& IF( AND( Q2<>"", AJ2<>"" ), CONCATENATE(", ", Q2," & ",AJ2), "")
& IF( AND( Q2<>"", AK2<>"" ), CONCATENATE(", ", Q2," & ",AK2), "")
& IF( AND( Q2<>"", AL2<>"" ), CONCATENATE(", ", Q2," & ",AL2), "")
& IF( AND( Q2<>"", AM2<>"" ), CONCATENATE(", ", Q2," & ",AM2), "")
& IF( AND( Q2<>"", AN2<>"" ), CONCATENATE(", ", Q2," & ",AN2), "")
& IF( AND( Q2<>"", AO2<>"" ), CONCATENATE(", ", Q2," & ",AO2), "")
& IF( AND( Q2<>"", AP2<>"" ), CONCATENATE(", ", Q2," & ",AP2), "")
& IF( AND( Q2<>"", AQ2<>"" ), CONCATENATE(", ", Q2," & ",AQ2), "")
& IF( AND( R2<>"", AB2<>"" ), CONCATENATE(", ", R2," & ",AB2), "")
& IF( AND( R2<>"", AC2<>"" ), CONCATENATE(", ", R2," & ",AC2), "")
& IF( AND( R2<>"", AD2<>"" ), CONCATENATE(", ", R2," & ",AD2), "")
& IF( AND( R2<>"", AE2<>"" ), CONCATENATE(", ", R2," & ",AE2), "")
& IF( AND( R2<>"", AF2<>"" ), CONCATENATE(", ", R2," & ",AF2), "")
& IF( AND( R2<>"", AG2<>"" ), CONCATENATE(", ", R2," & ",AG2), "")
& IF( AND( R2<>"", AH2<>"" ), CONCATENATE(", ", R2," & ",AH2), "")
& IF( AND( R2<>"", AI2<>"" ), CONCATENATE(", ", R2," & ",AI2), "")
& IF( AND( R2<>"", AJ2<>"" ), CONCATENATE(", ", R2," & ",AJ2), "")
& IF( AND( R2<>"", AK2<>"" ), CONCATENATE(", ", R2," & ",AK2), "")
& IF( AND( R2<>"", AL2<>"" ), CONCATENATE(", ", R2," & ",AL2), "")
& IF( AND( R2<>"", AM2<>"" ), CONCATENATE(", ", R2," & ",AM2), "")
& IF( AND( R2<>"", AN2<>"" ), CONCATENATE(", ", R2," & ",AN2), "")
& IF( AND( R2<>"", AO2<>"" ), CONCATENATE(", ", R2," & ",AO2), "")
& IF( AND( R2<>"", AP2<>"" ), CONCATENATE(", ", R2," & ",AP2), "")
& IF( AND( R2<>"", AQ2<>"" ), CONCATENATE(", ", R2," & ",AQ2), "")

 

[Scott Huish]

Instead of trying to insert that formula in VBA, why not write a function that does the same thing.

Function Concatenate2(range1 As Range, range2 As Range) As String
Dim c As Range, d As Range, s As String
For Each c In range1
    For Each d In range2
        If c <> "" And d <> "" Then s = s & c & " & " & d & ", "
    Next
Next
If Len(s) > 2 Then Concatenate2 = Left(s, Len(s) - 2)
End Function

In your spreadsheet:
=concatenate2(L2:R2,AB2:AQ2)

Excel 2010
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	a & b, a & c, a & d, a & e, b & b, b & c, b & d, b & e
2									a	b															b	c	d	e
Sheet1

Cell Formulas
Range	Formula
D1	=concatenate2(L2:R2,AB2:AQ2)

 

[cadillacben]

Thank you very much, that worked great. I was also able to use this in other areas, so it was double effective.