[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

[vbfbryce]

Put some code in to stop when it encounters that error, then see what the value of L+current row and A+current row+1 are.

[ActSciMan]

Thanks, actually I already have used the Immediate window to see the values. They both are long strings. I even tried:

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

and it works fine and does what I would expect it to do.

[vbfbryce]

Any way you can "dummy up" a small part of the data and then show us the workbook? Not sure what to suggest without seeing it behave. Does it always fail at the same place?

[ActSciMan]

Yes, it is stopping at the same place every time.

First part of the string which is in cell L CurrentRow is:
=T("<<PER PH* OTHER>>

Second part which is in Column A of next row is just a long string, no special characters of any sort (has , and .)

The error is happening when trying to set the value of L CurrentRow to the concatenation of these two values. The part that is weird is that this works fine for many other similar such concatenations that occur before it. All contain weird things like ">>". The first string always starts with =T("

[ActSciMan]

Well, I still don't understand why an error was caused but I believe I have fixed it. I created a string variable TempString to store the value in and concatenated with that... TempString = TempString & ... and then finally put that back in L CurrentRow. Now, it works.