CODEWITHSUNDEEP
vbaexcel
excel_amateur
excel_amateur

Reputation: 1

Excel VBA Macro Debugging - Application/Object-defined error (error 1004)

I'm working on debugging a macro for a colleague of mine and I was not the original writer of the code. That being said, the issue was created when excel updated, causing the macro to become defective - Application-defined or Object-defined error (ERROR '1004')

Here is a look at the specific portion of code:

Next n_counter

'calculate aggregated results
For counter_res = 1 To 7
    'insert variance of null distribution
        Worksheets("results").Cells(22 + counter_res, 2).Value = _
        Worksheets("L_S_2008").Cells(2 + counter_res, no_anchors).Value
    'mean values of agreement index
        Worksheets("results").Cells(22 + counter_res, 3).Value = _
        WorksheetFunction.Average(Range(Cells(3, 18 + counter_res), _
        Cells(5000, 18 + counter_res)))
    'SD of agreement index
        Worksheets("results").Cells(22 + counter_res, 4).Value = _
        (WorksheetFunction.Var(Range(Cells(3, 18 + counter_res), _
        Cells(5000, 18 + counter_res)))) ^ 0.5
    'P25 of agreement index
        Worksheets("results").Cells(22 + counter_res, 6).Value = _
        WorksheetFunction.Percentile(Range(Cells(3, 18 + counter_res), _
        Cells(5000, 18 + counter_res)), 0.25)
    'Median (P50) of agreement index
        Worksheets("results").Cells(22 + counter_res, 7).Value = _
        WorksheetFunction.Percentile(Range(Cells(3, 18 + counter_res), _
        Cells(5000, 18 + counter_res)), 0.5)
    'P25 of agreement index
        Worksheets("results").Cells(22 + counter_res, 8).Value = _
        WorksheetFunction.Percentile(Range(Cells(3, 18 + counter_res), _
        Cells(5000, 18 + counter_res)), 0.75)

Debugging returned the very first line of code

[Worksheets("results").Cells(22 + counter_res, 2).Value = Worksheets("L_S_2008").Cells(2 + counter_res, no_anchors).Value]

as the source of the error.

If anyone has some feedback or advice, I would be greatly honored. Thank you in advance for taking a look at the problem, it is much appreciated.

Upvotes: 0

Views: 1411

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

You can tighten up that code a lot by removing all of the repetition - that might help with debugging.

Possible causes of your current problem would be a bad value in no_anchors as noted by smackenzie, or maybe that the input sheet used for your calculations isn't the one you expect (since it's not explicitly specified)

Dim rngCalc As Range, wsf As WorksheetFunction
Set wsf = Application.WorksheetFunction


'calculate aggregated results
For counter_res = 1 To 7

    'What sheet is this intended to reference?
    'By default it will be the ActiveSheet unless specified
    Set rngCalc = Range(Cells(3, 18 + counter_res), _
                        Cells(5000, 18 + counter_res))

    With Worksheets("results").Rows(22 + counter_res)

        .Cells(2).Value = Worksheets("L_S_2008").Cells(2 + counter_res, _
                            no_anchors).Value           'variance

        .Cells(3).Value = wsf.Average(rngCalc)          'Mean
        .Cells(4).Value = wsf.Var(rngCalc) ^ 0.5        'SD
        .Cells(6).Value = wsf.Percentile(rngCalc, 0.25) 'P25
        .Cells(7).Value = wsf.Percentile(rngCalc, 0.5)  'Median(P50)
        .Cells(8).Value = wsf.Percentile(rngCalc, 0.75) 'P75

    End With

Next counter_res

Upvotes: 0

smackenzie
smackenzie

Reputation: 3022

Either the sheets are named incorrectly, or the variable no_anchors hasn't got a valid value for a column. When you debug, what value does no_anchors have...? If 0, that's the problem. Can't have a cell with a column value < 1.

Upvotes: 1

Related Questions