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

 

[azizrasul]

Thanks Norie, that worked, although I can't understand why my original code didn't work. Hmmmmm.

I don't use MS Excel a lot as I am a MS Access man. However I would be grateful if you can tell me why using Select is not good. I normally copy and paste the recorded macro code.

 

[Norie]

Aziz

Select just isn't needed but it's probably unqualified references that actually cause the real problems.

Mind you the two seem to go hand in hand and they do affect each other.

I assume at some point you've converted Access macros to VBA?

If you have you'll know that though the code is usually OK it sometimes isn't quite right and you need to change it.

Now in Excel when recording a macro it's the same but it's recording all the actions you take rather than converting existing code.

So you get lots of stuff you just don't need and you need to change a lot more.

 

[azizrasul]

I understand.