+ Reply to Thread
Results 1 to 7 of 7

Runtime error 1004 & runtime error 424

Hybrid View

  1. #1
    Registered User
    Join Date
    11-17-2017
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    14

    Runtime error 1004 & runtime error 424

    Hi I am getting this error in this code which is part of a larger macro. It always stops here giving the runtime error 1004 or 424 message. The macro sometimes gets through the whole sub routine and other times it gives this error. Thanks for your help.

    'Attach brandtags
    
    Dim lastRow As Long
        With Sheets("OUT_main")
            lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
            .Range("D2:G2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,2,0)"
            .Range("E2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,3,0)"
            .Range("F2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,4,0)"
            .Range("G2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,5,0)"
            .Range("D2:G2").AutoFill Destination:=.Range("D2:G" & lastRow)
            .Range("B2:B" & lastRow).FormulaR1C1 = "=INDEX('48_OUT'!C,MATCH(RC[1],'48_OUT'!C[1],0))"
        End With
            Sheets("48_OUT").Select
            Range("A1").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy
            Sheets("OUT_Main").Select
            Range("A1").Select
            ActiveSheet.Paste
            Range("A1").Select
            Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            Application.DisplayAlerts = False
            Sheets(Array("1 (1)", "Market Share adj", "48_OUT")).Select
            Sheets("48_OUT").Activate
            ActiveWindow.SelectedSheets.Delete
            Sheets("OUT_Main").Select
            Application.CutCopyMode = False
            Selection.AutoFilter
    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,529

    Re: Runtime error 1004 & runtime error 424

    It stops where? It would be easier to test the code if you supplied a workbook sample

  3. #3
    Registered User
    Join Date
    11-17-2017
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    14

    Re: Runtime error 1004 & runtime error 424

    The workbook is confidential it must be deleted after. It stops at

    Range(Selection, Selection.End(xlToRight)).Select

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,529

    Re: Runtime error 1004 & runtime error 424

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Runtime error 1004 & runtime error 424

    Perhaps this instead
        Dim lastRow As Long
        With Sheets("OUT_main")
            lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
            .Range("D2:G2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,2,0)"
            .Range("E2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,3,0)"
            .Range("F2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,4,0)"
            .Range("G2").FormulaR1C1 = "=VLOOKUP(RC3,'48_OUT'!R2C3:R3163C7,5,0)"
            .Range("D2:G2").AutoFill Destination:=.Range("D2:G" & lastRow)
            .Range("B2:B" & lastRow).FormulaR1C1 = "=INDEX('48_OUT'!C,MATCH(RC[1],'48_OUT'!C[1],0))"
            Sheets("48_OUT").Range("A1", Sheets("48_OUT").Range("A1").End(xlToRight)).Copy .Range("A1")
            With .UsedRange
                .Value2 = .Value2
                .AutoFilter
            End With
        End With
        Application.DisplayAlerts = False
        Sheets(Array("1 (1)", "Market Share adj", "48_OUT")).Delete
        Sheets("OUT_Main").Select
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Registered User
    Join Date
    11-17-2017
    Location
    Madrid
    MS-Off Ver
    2016
    Posts
    14

    Re: Runtime error 1004 & runtime error 424

    Thank you for your response.

    There is a compile error at .usedrange. Also did you include the paste values in the code?

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Runtime error 1004 & runtime error 424

    That is what this line does
                .Value2 = .Value2
    The syntax of that code is fine- can you copy and paste here the exact code you have now?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime error '1004': Paste special method of range class error.
    By Daryl Zer0 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-05-2014, 01:44 AM
  2. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM
  3. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  4. runtime error 1004 general odbc error
    By habj04ae in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-24-2012, 05:59 AM
  5. Proper error catching runtime error 1004
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2010, 01:07 AM
  6. Runtime error '1004' General ODBC error
    By star_lucas in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-03-2008, 05:10 PM
  7. [SOLVED] Excel 2003 Macro Error - Runtime error 1004
    By Cow in forum Excel General
    Replies: 2
    Last Post: 06-07-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1