I am using the Concatenation code I got (and modified into a function but I have not altered the code that concatinates) by @jindon, it works wonderfully.
The problem is that the column of concatenated cells throws a error in a different Macro that matches columns even though the concatinated column is not one of the matching columns of the matching macro.
If I delete the column of concatenated cells the matching Macro work fine.
The error ocrurs at this point in the matching macro
wsPB.Range("A" & j).Resize(1, sLC).Offset(1, pbOffset).Value = Application.Index(arrS, i, 0)
I am pretty sure the problem is
Application.Index(arrS, i, 0)
Is their a way to get the Concatenate code below to not produce this error when it encounters
Application.Index(arrS, i, 0)
Thank you
'Concatenate
'Sheet Name
'Delimeter
'Post Back Column
'Range
'Insert Column For Post Back Boolean
Sub Concat()
ConCatX "XXX", "|", "B", "NewHeaderName", Range("B2,A2,D2,N2,L2"), True
End Sub
Function ConCatX(shtName As String, dilm As String, PBcol As String, NewHeaderName As String, Rng As Range, Optional pbInsertCol As Boolean = False)
Dim ws As Worksheet
Dim R As Range, i As Long
Set ws = ThisWorkbook.Sheets(shtName)
If pbInsertCol = True Then
'Insert Column For Post Back
ws.Columns(CLTCN(PBcol)).insert
ElseIf pbInsertCol = False Then
End If
If Rng Is Nothing Then Exit Function
With ws.Cells(1).CurrentRegion
ReDim a(1 To .Rows.Count, 1 To 1)
a(1, 1) = NewHeaderName
For i = 2 To .Rows.Count
For Each R In Rng
If .Cells(i, R.Column) <> vbNullString Then
a(i, 1) = a(i, 1) & IIf(a(i, 1) = "", "", dilm) & .Cells(i, R.Column).Value
End If
Next
Next
With .Offset(, CLTCN(PBcol) - 1).Resize(, 1)
.Value = a
End With
End With
End Function
Bookmarks