[Excel VBA] Error 1004

[ActSciMan]

Hello

I am working on some code and it sometimes gives Error 1004 and I can not for the life of me figure out why. I have a bunch of data and the data in Column L (a string) sometimes gets broken into pieces and spread across 2 or more lines. This code is supposed to fix it and put everything back together how it should be.

So, if I'm in a certain row, CurrentRow, sometimes the string in Column L is broken up so that part of it is in the correct spot, but then a bit more of it is in row CurrentRow + 1 in column A. There may be more in rows CurrentRow + 2, CurrentRow + 3, and so on, but after 4 or 5 rows, we're usually done. The rest of the data that is supposed to appear in CurrentRow columns M through AA is on the last row containing the data from column L in Columns B through whatever.

The error always occurs at this line of code:

.Range("L" & CurrentRow).Value = .Range("L" & CurrentRow).Value & .Range("A" & CurrentRow + 1).Value

It works on most lines where the data is broken up but sometimes it doesn't. I can't give actual data where it messes up because it is policy data. But, there's nothing suspicious about it to me. It's a string. Any ideas come to mind? Thanks for any help

Sub ScrubNewData2()
    Application.ScreenUpdating = False
    Dim LastRow As Long, CurrentRow As Long, MessedUpRows As Long
    Dim YearCellValue As String, DescripCellValue As String
    Dim LastNonCurrencyCol As String, FirstCurrencyCol As String, LastCol As String

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' These are the only things that need to be changed from spreadsheet to spreadsheet
    LastNonCurrencyCol = "U"
    FirstCurrencyCol = "V"
    LastCol = "AA"
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    With Worksheets("New")

        NumCellsCopied = .Range("M1", LastCol & "1").Count
        LastRow = .Range("A500000").End(xlUp).Row
        
        CurrentRow = 2
        Do Until CurrentRow > LastRow
            If Left(.Range("L" & CurrentRow).Value, 3) = "=T(" Then
                
                
                ' Count number of rows messed up
                MessedUpRows = 1
                If .Range("B" & CurrentRow + 1) = "" Then
                    MessedUpRows = .Range("B" & CurrentRow).End(xlDown).Row - CurrentRow
                    For i = 1 To MessedUpRows - 1
                        If .Range("A" & CurrentRow + 1) <> "" Then
                            .Range("L" & CurrentRow).Value = .Range("L" & CurrentRow).Value & .Range("A" & CurrentRow + 1).Value
                        End If
                        
                        .Range("A" & CurrentRow + 1).EntireRow.Delete
                        LastRow = LastRow - 1
                        
                            
                    Next i
                End If
                .Range("L" & CurrentRow).Value = .Range("L" & CurrentRow).Value & .Range("A" & CurrentRow + 1).Value
                .Range("L" & CurrentRow).Copy
                .Range("L" & CurrentRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False

    
                .Range("M" & CurrentRow, LastCol & CurrentRow).Value = .Range("B" & CurrentRow + 1).Resize(1, NumCellsCopied).Value
                .Range("A" & CurrentRow + 1).EntireRow.Delete
                LastRow = LastRow - 1
            End If
            CurrentRow = CurrentRow + 1
        Loop
        
    End With
End Sub