Range Class failed

[azizrasul]

I have the following code, where I get an error 'Range Class failed' on the line marked 'ERROR ON THIS LINE in the code below.

Can anyone help?

Public Sub CreateBars(intKPINumber As Integer)

    Dim x As Integer,  y As Integer,  intRows As Integer, intCells As Integer, intCellsDetailed As Integer
    Dim rng As Range
    Dim strMonthValue As String
    Dim dblBarValue As Double
    Dim lngColour As Long    
    
    On Error GoTo ErrorHandler
    
    Select Case intKPINumber
        Case 5
            Sheets("KPI 5 - WBT").Select
            Set rng = Selection.Parent.UsedRange
            strMonthValue = Sheets("KPI 5 - WBT").Cells(rng.Rows.Count, 1).Value
            dblBarValue = Sheets("KPI 5 - WBT").Cells(rng.Rows.Count, 4).Value
            intCells = 225 - (100 * dblBarValue / 5)
            intCellsDetailed = 110 - (100 * dblBarValue / 5)
            
            If dblBarValue <= 3.625 Then
                lngColour = vbRed
            ElseIf dblBarValue > 3.625 And dblBarValue <= 3.875 Then
                lngColour = vbYellow
            ElseIf dblBarValue > 3.875 Then
                lngColour = vbGreen
            End If
            
            For y = 2 To 13
                If Sheets("DASHBOARD 2011-12").Cells(124, y).Value = strMonthValue Then
                    Sheets("DASHBOARD 2011-12").Select
                    Range(Cells(intCells, y), Cells(225, y)).Select
                    With Selection
                        .HorizontalAlignment = xlCenter
                        If lngColour = vbRed Then
                            .VerticalAlignment = xlBottom
                        ElseIf lngColour = vbYellow Then
                            .VerticalAlignment = xlCenter
                        ElseIf lngColour = vbGreen Then
                            .VerticalAlignment = xlTop
                        End If
                        .WrapText = False
                        .Orientation = 90
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                        .MergeCells = True
                    End With
    
                    ActiveCell.FormulaR1C1 = dblBarValue
                    Selection.NumberFormat = "0.00"
                    
                    Sheets("DETAILED - KPI 5").Select
'ERROR ON THIS LINE Range(Cells(intCellsDetailed, y), Cells(110, y)).Select
                                                                   
                    With Selection
                        .HorizontalAlignment = xlCenter
                        If lngColour = vbRed Then
                            .VerticalAlignment = xlBottom
                        ElseIf lngColour = vbYellow Then
                            .VerticalAlignment = xlCenter
                        ElseIf lngColour = vbGreen Then
                            .VerticalAlignment = xlTop
                        End If
                        .WrapText = False
                        .Orientation = 90
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                        .MergeCells = True
                    End With
    
                    With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .PatternTintAndShade = 0
                        If lngColour = vbYellow Then
                            .Color = 65535
                            .TintAndShade = 0
                        ElseIf lngColour = vbRed Then
                            .Color = 255
                            .TintAndShade = 0
                        ElseIf lngColour = vbGreen Then
                            .Color = 6750054
                            .TintAndShade = 0
                        End If
                    End With

                    ActiveCell.FormulaR1C1 = dblBarValue
                    Selection.NumberFormat = "0.00"
                    Exit For
                End If
            Next y
    End Select
    
ErrorHandler:
    If Err.Number = 1004 Then
        MsgBox Err.Description
    End If
    
End Sub

 

[wigi]

- What is the value of intCellsDetailed at the time of the error?

- Why do you get the error? You have the line of code: On Error GoTo ErrorHandler
Or do you mean that at that point in the code, the code execution jumps to the error handler?

- Do not Select, or use ActiveCell, Selection and so on. It remains that without them, you still need to work with valid ranges in your code, but you will be able to much more streamline the code, use less hardcoded elements and the code will run better and more smoothly.

- Last but not least, merging cells in Excel is evil. Not more, not less.

 

[Norie]

The error is probably because you aren't referencing things correctly.

Using Select/Selection etc don't help really.

Merged cells, I think Wigi's covered that.

Oh, and lose the On Error... stuff.

That could just be hiding errors.

 

[azizrasul]

intCellsDetailed = 34.

I'm using merged cells because I'm creating a dynamic bar chart where the bars will have the value.

I take the point regarding not using Select etc., but I don't know how to replace the code without it. I'm not so savvy with Excel VBA code. For example how would I change the code snippet below?

I have placed the error handler for the moment so I can see what the error is.

                    With Selection
                        .HorizontalAlignment = xlCenter
                        If lngColour = vbRed Then
                            .VerticalAlignment = xlBottom
                        ElseIf lngColour = vbYellow Then
                            .VerticalAlignment = xlCenter
                        ElseIf lngColour = vbGreen Then
                            .VerticalAlignment = xlTop
                        End If
                        .WrapText = False
                        .Orientation = 90
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                        .MergeCells = True
                    End With

 

[Norie]

Aziz

Your error handler is only really set you up to report the 1004 error.

Any other error won't get reported and will essentially stop the code.

Stopping the code probably isn't to drastic, but not knowing why, where etc won't help much.

 

[wigi]

You'd improve the code in 2 steps.

STEP 1:

Note that you missed 2 lines above the code you quoted.

With Sheets("DASHBOARD 2011-12")
    With .Range(.Cells(intCells, y), .Cells(225, y))
        .HorizontalAlignment = xlCenter
        If lngColour = vbRed Then
            .VerticalAlignment = xlBottom
        ElseIf lngColour = vbYellow Then
            .VerticalAlignment = xlCenter
        ElseIf lngColour = vbGreen Then
            .VerticalAlignment = xlTop
        End If
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End With

Now you do not select anymore.

STEP 2:

Make 3 helper cells in a (hidden) area. They contain the required formatting.
In your code, you just copy 1 of the cells, instead of setting all the properties of the cells one after the other.

The maintenance of your application/code will be much easier afterwards.

Except the first 2 lines and 2 last lines in my code in this post, all lines can be removed with 1 or 2 lines of code to do the copy. Copy a cell or only its formats.

I leave it to you to use the macro recorder for the code syntax and to browse other topics on the forum here.

 

[azizrasul]

I placed the error handler specifically for the 1004 error just to avoid entering Err.Number in the debug window, that's all. It will be removed when I solve the current problem.

Any ideas of how to change the code to avoid the error? How do I replace the line

With Selection

 

[wigi]

Any ideas of how to change the code to avoid the error? How do I replace the line

With Selection

See above.

 

[azizrasul]

I understood Step 1. Many thanks for that as I was struggling searching the internet to see how to do this.

In Step 2 you mention 3 helper cells? Why 3?

I couldn't understand what you meant by

Except the first 2 lines and 2 last lines in my code in this post, all lines can be removed with 1 or 2 lines of code to do the copy. Copy a cell or only its formats.

 

[wigi]

One for VerticalAlignment = xlBottom, one for xlCenter, one for xlTop.