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

Question

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.

smackenzie · Accepted Answer · 2015-02-13 21:43:25Z

1

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.

answered Feb 13, 2015 at 21:43

smackenzie

3,0829 gold badges60 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2015-02-14 00:32:27Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related