Application-defined or object-defined error

[azizrasul]

I have the following code, where I get the error (Application-defined or object-defined error) on the line

ActiveSheet.Range(Cells(6, z), Cells(10, z)).Select. What am I doing wrong?

    For Each rng In Range("DateRange")
        If strDateRange <> rng.Value Then
            strDateRange = rng.Value
            Cells(8, 8 + x).Value = rng.Value
            Range(Cells(8, 8 + x), Cells(8, 9 + x)).Select
            Selection.Merge
            Selection.HorizontalAlignment = -4108
            Selection.Font.Bold = True
            Cells(9, 8 + x).Value = "Baseline"
            Cells(10, 8 + x).Value = "Actual"
            Cells(10, 9 + x).Value = "Target"
            Sheets("TARGETS").Select
            ActiveSheet.Cells(5, 3).Select
            For z = 3 To 12
                If ActiveSheet.Cells(5, z) = rng.Value Then
                    ActiveSheet.Range(Cells(6, z), Cells(10, z)).Select
                    Selection.Copy
                    Sheets("KEY PERFORMANCE INDICATORS").Select
                    Cells(11, 9 + x).Select
                    ActiveSheet.Paste
                    Exit For
                End If
            Next z
            x = x + 2
        End If
    Next rng

 

[Joe4]

Is this is a standard VBA module, or a Sheet VBA module?
Do you have any merged cells or protected/locked cells in this range?

 

[azizrasul]

The code is run by pressing a command button on the sheet "KEY PERFORMANCE INDICATORS".
No.

 

[Joe4]

The code is run by pressing a command button on the sheet "KEY PERFORMANCE INDICATORS".

OK, but that command button is linked to the code you posted, right? The VBA code you posted is located in a VBA module. There are different kind of VBA modules. If you have it in the wrong one, your code will not work!

Go into your VB Editor, view the VBA Project Explorer and see what kind of Module your code is found in. Is it under "Modules" or "Sheet1..." or "ThisWorkbook"?

 

[azizrasul]

It's not under Modules.

It's under

Microsoft Excel Objects
Sheet1("KEY PERFORMANCE INDICATORS")

The code begins as

Private Sub cmdRun_Click()

..
..

End Sub

 

[Joe4]

Yep, just as I thought. I helped someone just a few days ago with the same problem. If you put a module that covers multiple sheets into a sheet module like you have, it will not work properly (sheet modules are just for code that affects ONLY that particular sheet).

If the code covers multiple sheets, you will need to move it to a standard module. Just click in the VBA Project Explorer, select Insert, then select Module. Then, cut and paste your VBA code from the current location to this new module. Then it should work properly.

 

[azizrasul]

Thanks, I will try that.

 

[azizrasul]

When I try that and press the command button, nothing happens!

 

[Joe4]

Since you moved the VBA code, you might need to re-connect the button to the macro. If you right click on the button, does it give you the option to re-map/attach a macro?

At worst, you may just need to create a new command button, which should prompt you to attach a macro, and then you can select the revised macro.

 

[Norie]

The problem is probably because of the use of unqualified cell references, and ActiveSheet might not help either.

For example here both Cells are unqualified:

ActiveSheet.Range(Cells(6, z), Cells(10, z)).Select

This should work, at least it shouldn't error anyway:

With ActiveSheet
      .Range(.Cells(6, z), .Cells(10, z)).Select
End With

As well as that the rest of the code really needs to be looked at, all that Select stuff just isn't a good idea.

I was going to have a quick go at redoing some of it but after a good look decided not to - I couldn't follow what was happening.