none
Excel VBA run-time error 1004 : Application-defined or object-defined error RRS feed

  • Question

  •  I am developing a vba code and every time i run it this error show in my computer:

                      run-time error 1004 : Application-defined or object-defined error

    on this part of the code:

            max_zx(x, z) = Application.WorksheetFunction.Max(Worksheets(username).Range(Cells(55, 22 + w + z), Cells(55 + nalt, 22 + z + w)))
            Worksheets(username).Cells(60, 22).Offset(0, z + w) = max_zx(x, z)

    Username is a variable and the sheet where the code is insert is a copy of other one. Basically username is the name that we  give to the copy sheet.

    I read something that this is problem is related with copy and past data. But i'm not an expert.

    Note: Every sheet must be in the same file.

    Can you help to fix this?

    Thanks in advance.

    Monday, April 21, 2014 7:58 PM

All replies

  • re:  runtime error 1004

    You have not specified the worksheet for the "Cells" callout - so they refer to the Active Sheet.
    Application.WorksheetFunction.Max(Worksheets(username).Range(Cells(55, 22 + w + z), Cells(55 + nalt, 22 + z + w)))
    Should be...
    '---
    With Worksheets(username)
      Application.WorksheetFunction.Max(.Range(.Cells(55, 22 + w + z), .Cells(55 + nalt, 22 + z + w)))
    End With
    '---
    Note the dot in front of Range and both Cells.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 1:36 AM
    Monday, April 21, 2014 11:14 PM