VBA Error 2029 Mismatch Type

[TylerCross]

Hello,
I am currently creating some vba code to concatenate rows in a certain column into one. When I run two different data, the first one works but then the second one does not work and I receive a error message. My code is
Sub mergeCategoryValues()
Dim lngRow As Long

With Sheet1
Dim columnToMatch As Integer: columnToMatch = 1
Dim columnToConcatenate As Variant: columnToConcatenate = 22
Dim columnToSum As Integer: columnToSum = 2

lngRow = .Cells(65536, columnToMatch).End(xlUp).Row
.Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes

Do
If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then
.Cells(lngRow - 1, columnToConcatenate) = .Cells(lngRow - 1, columnToConcatenate) & "; " & .Cells(lngRow, columnToConcatenate)
.Cells(lngRow - 1, columnToSum) = .Cells(lngRow - 1, columnToSum) + .Cells(lngRow, columnToSum)
.Rows(lngRow).Delete
End If

lngRow = lngRow - 1
Loop Until lngRow = 1
End With
MsgBox (" Macro Complete! ")

End Sub

The red text is where the error is occurring. I am using a Variant data type in the rows it contains numbers and text.
Any help is greatly appreciated.

 

[Norie]

When you get the error what's actually in the cells you are trying to concatenate?

 

[JLGWhiz]

I think I would declare 'columnToConcatenate' as a long integer rather than variant:

Dim columnToConcatenate As Long

and then add .Value to the cell references you are concatenating.

.Cells(lngRow - 1, columnToConcatenate) = .Cells(lngRow - 1, columnToConcatenate).Value & "; " & .Cells(lngRow, columnToConcatenate).Value

 

[TylerCross]

When you get the error what's actually in the cells you are trying to concatenate?

I am using the first column as the "transaction number" If there are multiple items it will have more then one row with the same transaction number, column 22 is "text notes" so it could either provide a reason why there is a transaction or a receipt number

 

[Norie]

Are there any errors in the columns you trying to concatenate?

 

[TylerCross]

Are there any errors in the columns you trying to concatenate?

.Cells(lngRow - 1, columnToConcatenate) = Error 2029
This is the error that occurs on the Concatenate line.
When looking at the column there is not but when applying a filter there is #NAME?

 

[Norie]

The #NAME? is the problem, you can't concatenate an error value with another value.

 

[TylerCross]

The #NAME? is the problem, you can't concatenate an error value with another value.

Thank you for your help. Do you know why this error is occurring. When selecting this the #NAME? no data appears in any of the columns.

 

[Norie]

What's the formula in the cell that's showing #NAME? ?

 

[TylerCross]

What's the formula in the cell that's showing #NAME? ?

There are no formulas in the cells