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

 

[Norie]

Aziz

Why not remove the error message now?

If there are any other errors before the one you are looking out for then they won't get reported.

If you insist on keeping it remove the If statement.

 

[azizrasul]

OK thanks wigi, I understand now.

 

[azizrasul]

I have removed the error handler as it was always temporary..