none
Run-Time error 1004 - VBA -

    Question

  • Hopefully an easy one to answer... no idea why I am getting this issue and I can't figure it out.  

    Oddly, the code runs fine through "Step 1", but throws and error on "Step 2" (Run-time error '1004' Application defined or object-defined error".  Even more interesting is that when I go to debug, I find that Worksheets(posSheetName).Range("L:U").Value has actually been updated correctly!

    Code:

    Private Sub CmdPos()
    
    Dim LongShort, rowB As Integer, r as Long
    
    Application.ScreenUpdating = False
    
    Worksheets("POS").Cells.Clear
    
    Worksheets("POS").Range("A:N").Value = Worksheets("bluebook").Range("A:N").Value  'Step 1 - no issues
     
    
    Worksheets("POS").Activate
    
    Range("A:D,F:F,M:M").EntireColumn.Delete
    
    
    
    Cells.Sort Key1:=Range("F1")
    LongShort = Cells(Rows.Count, "F").End(xlUp).row
        
        With Range("F1:F" & LongShort)
            .AutoFilter Field:=1, Criteria1:="=C", Operator:=xlOr, Criteria2:="=D"
            .Offset(1).Resize(LongShort).EntireRow.Delete
            .AutoFilter
        End With
    
    'Delete new Column F and I(unused data)
    Range("F:F,I:I").EntireColumn.Delete
    
    r = Cells(Rows.Count, 1).End(xlUp).row
        With Sheets("POS")
            Range("C2:C" & r) = Range("C2:C" & r).Value
            For r = r To 2 Step -1
                If Cells(r, 3) = Cells(r - 1, 3) And Cells(r, 6) = Cells(r - 1, 6) Then
                    Cells(r - 1, 2) = Cells(r, 2) + Cells(r - 1, 2)
                    Cells(r - 1, 5) = Cells(r, 5) + Cells(r - 1, 5)
                    Cells(r, 3).EntireRow.Delete xlUp
                End If
            Next r
        End With
    
    Worksheets("POS").Range("L:U").Value = Worksheets("yes").Range("A:J").Value 'Step 2 - Runtime error 1004
    
    
    
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    
    
    
    Application.ScreenUpdating = True
    End Sub

    Tuesday, November 25, 2014 7:17 PM

Answers

  • It is impossible to re-create your error without the workbook - the code itself works fine, so there is something about the two ranges

    Worksheets("POS").Range ("L:U")
    and
    Worksheets("yes").Range ("A:J")

    that is causing the error - merged cells, objects, weird errors, workbook corruption...

    Anyways, try changing

    Worksheets("POS").Range("L:U").Value = Worksheets("yes").Range("A:J").Value

    to
    With Worksheets("yes")
        Intersect(.Range("A:J"), .UsedRange).Copy
        Worksheets("POS").Range("L1").PasteSpecial xlPasteValues
    End With



    Tuesday, November 25, 2014 8:11 PM

All replies

  • It is impossible to re-create your error without the workbook - the code itself works fine, so there is something about the two ranges

    Worksheets("POS").Range ("L:U")
    and
    Worksheets("yes").Range ("A:J")

    that is causing the error - merged cells, objects, weird errors, workbook corruption...

    Anyways, try changing

    Worksheets("POS").Range("L:U").Value = Worksheets("yes").Range("A:J").Value

    to
    With Worksheets("yes")
        Intersect(.Range("A:J"), .UsedRange).Copy
        Worksheets("POS").Range("L1").PasteSpecial xlPasteValues
    End With



    Tuesday, November 25, 2014 8:11 PM
  • Thanks, Bernie.  For a range with 2000-3000 rows of data, would copy and pasting result in decreased performance?  I've been trying to avoid using the clipboard, due to what I *think* is possible memory leak from some part of this or another sub.
    Tuesday, November 25, 2014 8:46 PM
  • I have not noticed a difference, as long as you do complete ranges at once. I think part of your problem may be that the code is actually affecting millions of cells when you use the Column.value = column.Value version.  You also use this line, which helps:

    Application.CutCopyMode = False

    Tuesday, November 25, 2014 9:06 PM