Run-time error '1004': Application-defined or object-defined error-VBForums
Results 1 to 12 of 12

Thread: Run-time error '1004': Application-defined or object-defined error

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2005
    Posts
    6

    Run-time error '1004': Application-defined or object-defined error

    Hi,

    Another novice problem from me.
    I get this error message:
    Run-time error '1004':
    Application-defined or object-defined error

    This is the statement that is highlighted when the error comes up.
    ActiveCell.FormulaR1C1 = "=Average(R[6]C[-2]:R[myDatasecs+6]C[-2])"

    Here is the code block:

    Set currentCell = Range("Q11")
    For n = 1 To 10
    currentCell.Value = myOscArray(n, 1)
    currentCell.Offset(0, 1).Value = myOscArray(n, 2)
    currentCell.Offset(0, 2).Formula = "=RC[-2]-RC[-1]"
    Set nextCell = currentCell.Offset(1, 0)
    Set currentCell = nextCell
    Next n
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=Average(R[6]C[-2]:R[myDatasecs+6]C[-2])"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "Mean Flow"

    Thanks in advance,
    Neil

  2. #2
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    590

    Re: Run-time error '1004': Application-defined or object-defined error

    Suggestion - turn on the macro recorder (Tools->Macro->Record New Macro) and type in the formula you want in the cell. Stop the recorder and see what you get. Maybe you already did this.......

    VBAhack

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2005
    Posts
    6

    Re: Run-time error '1004': Application-defined or object-defined error

    Yeah. I already did that. The problem is that the amount of data that is taken into the spreadsheet is of varying length. So, I can't use a set of pre-programmed cell identities. The macro needs to detect the data that is important in the analysis.

    Thanks for the advice. I think that I'm going to have to approach this a different way.

    Neil.

  4. #4
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: Run-time error '1004': Application-defined or object-defined error

    The line that gives you the error:

    ActiveCell.FormulaR1C1 = "=Average(R[6]C[-2]:R[myDatasecs+6]C[-2])"

    This doesn't make sense ... is the "R1C1 =" actuallly in the line? It isn't correct as shown.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Excel Hell!
    Posts
    4,973

    Re: Run-time error '1004': Application-defined or object-defined error

    Quote Originally Posted by neilma
    Code:
        Set currentCell = Range("Q11")
        For n = 1 To 10
            currentCell.Value = myOscArray(n, 1)
            currentCell.Offset(0, 1).Value = myOscArray(n, 2)
            currentCell.Offset(0, 2).Formula = "=RC[-2]-RC[-1]"
            Set nextCell = currentCell.Offset(1, 0)
            Set currentCell = nextCell
            Next n
        Range("E6").Select
        ActiveCell.FormulaR1C1 = "=Average(R[6]C[-2]:R[" & myDatasecs +6 & "]C[-2])"
        Range("F6").Select
        ActiveCell.FormulaR1C1 = "Mean Flow"
    You have answered your own question...


    Hope that helps...


    Edit:
    The error is a generic one, but it is caused usually when you try to put in functions that don't work for some reason (such as missing brackets or using variables instead of numbers).
    Its not the most helpful of errors and that you have pinpointed it to that particular line suggests something is wrong with your formula (which I highlighted as possible suspect).
    If you are unsure, then put the formula into a string variable, debug.print the variable, and copy that formula from the immediates window (ctrl+g) to a cell in excel. If it errors with a more informative error msg, good for you, otherwise it should work/not work and you'll see. It might be something simple. Post up if it still doesn't work.
    Last edited by Ecniv; May 10th, 2005 at 04:17 AM.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  6. #6
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: Run-time error '1004': Application-defined or object-defined error

    What is myDatasecs? What is the Dim statement for it? How is it calculated?

    Are you trying to average a range where the bottom of the range depends on the number in some other cell that can be changed by the user?
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  7. #7
    New Member
    Join Date
    Jun 2005
    Posts
    1

    Re: Run-time error '1004': Application-defined or object-defined error

    Hi!

    I have also had the sameproblem for a modified recorded Excel Macro.
    I also needed a variable inside the on the brackets in the ActiveCell.FormulaR1C1 statement. After encountered the same error message I debuged it. The first time you entered the loop, ActiveCell.FormulaR1C1 had some corny value. I never found out why or where this value came from. By adding the streng = "=IF(R[-1]C[3]=0,1,0)" in front of the "Do While" loop it worked fine.


    Dim rad As Long
    Dim kolonne As Integer
    Dim teller As Long
    Dim streng As String
    Dim inverter As Byte
    Dim chipnr As Integer


    teller = 0
    test1 = 0
    streng = "=IF(R[-1]C[3]=0,1,0)"
    Do While teller < 62400

    rad = -38 - (teller - Int(teller / 2400))
    chipnr = Int((teller - Int(teller / 300) * 300) / 20)
    Select Case chipnr
    Case 4, 6, 9, 10, 12 To 14
    inverter = 1
    Case Else
    inverter = 0
    End Select

    kolonne = 3 + Int((teller - Int(teller / 2400) * 2400) / 300)
    streng = "=IF(R[" & rad & "]C[" & kolonne & "]=" & inverter & ",1,0)"

    ActiveCell.FormulaR1C1 = streng

    ActiveCell.Offset(1, 0).Range("A1").Select
    teller = teller + 1
    Loop



    bJøRn

  8. #8
    New Member
    Join Date
    Jan 2009
    Posts
    1

    Re: Run-time error '1004': Application-defined or object-defined error

    One of the reasons for this error is as I ran a macro as under
    Sub Macro1()
    On Error GoTo finderror
    Dim I As Long
    For I = 65500 To 66000
    Cells(I, 1) = I
    Next I
    Exit Sub

    finderror:
    MsgBox Err.Number & " " & Err.Description

    '
    End Sub
    Hope you find this helpful

  9. #9
    New Member
    Join Date
    Jan 2010
    Posts
    1

    Re: Run-time error '1004': Application-defined or object-defined error

    I had the same error. However, when I pasted the formula calculated by my code into the cell, it worked perfectly. I don't know the reason for the error. Eventually, I got rid of it by simply changing FormulaR1C1 to FormulaR1C1Local.

  10. #10
    New Member
    Join Date
    Mar 2010
    Posts
    2

    Re: Run-time error '1004': Application-defined or object-defined error

    I very new to this and I am getting this error with the following code can anyone help:
    Columns("A").End(x1Down).Select

    from:

    Private Sub CommandButton1_Click()
    Columns("A").End(x1Down).Select
    ActiveCell.Offset(1, 0).Value = ContactName.Text
    ActiveCell.Offset(1, 1).Value = Address1.Text
    ActiveCell.Offset(1, 2).Value = "K"
    Unload UserForm2

    End Sub

  11. #11
    New Member
    Join Date
    Mar 2010
    Posts
    2

    Re: Run-time error '1004': Application-defined or object-defined error

    I am trying to set up a user form where i can enter a customers contact details and event details and then unload the details into a spreadsheet.

    I would also like to be able to select a returning customers address details and add a new event to the spreadsheet (so i only have to type the address once but create a new record) I have no idea how to do this or if its possible

    Oh and each entry or event needs a unique reference number

    and all this needs to be searchable, i know i could do this in access but i don't like it

    Hope some one can help me - please

  12. #12
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Re: Run-time error '1004': Application-defined or object-defined error

    Hi,

    Can anyone tell me what's wrong with this coding? I created macro on version 2003 and it work exactly how I want it to run. But I keep getting run time error message even though I do the same thing for version 2010.

    Thanks!

    Sub ready()
    '
    ' ready Macro
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Daily inventory report!R6C1:R327C22", Version:=xlPivotTableVersion10). _
    CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable5" _
    , DefaultVersion:=xlPivotTableVersion10
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Ship To")
    .Orientation = xlPageField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Width")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Thick")
    .Orientation = xlRowField
    .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
    "PivotTable5").PivotFields("Wgt Lb "), "Count of Wgt Lb ", xlCount
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
    "PivotTable5").PivotFields("Pieces"), "Count of Pieces", xlCount
    With ActiveSheet.PivotTables("PivotTable5").DataPivotField
    .Orientation = xlColumnField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of Wgt Lb ")
    .Caption = "Sum of Wgt Lb "
    .Function = xlSum
    End With
    Range("D5").Select
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable5").ShowDrillIndicators = False
    ActiveSheet.PivotTables("PivotTable5").TableStyle2 = "PivotStyleMedium14"
    ActiveSheet.PivotTables("PivotTable5").ShowTableStyleRowHeaders = False
    Range("D3").Select
    ActiveSheet.PivotTables("PivotTable5").CalculatedFields.Add "Wgt in tons", _
    "='Wgt Lb ' /2000", True
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Wgt in tons").Orientation _
    = xlDataField
    Range("E5").Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Sum of Wgt in tons")
    .NumberFormat = "0.00"
    End With
    End Sub

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.