Code error due to less values

**roberto1111** · 11-08-2009, 02:16 PM

Good evening friends,

The code down below selects the 4 lowest values in Range("A1:Z1") and gives them a red color.

The problem; if there are only 1,2 or 3 values in Range("A1:Z1") instead of 4 then it errors.

My query; how should the code be modified so it also works when there are less then 4 values to process?

Thank you for any help offered!

Greetings from Roberto, The Netherlands

Code:

    Dim oneCell As Range
    Dim smallVal4 As Double
    With Sheet1.Range("D1:RD1")
    smallVal4 = Application.Small(.Cells, 4)
    For Each oneCell In .Cells
    If Val(CStr(oneCell.Value)) <= smallVal4 Then oneCell.Font.ColorIndex = 3 
    If oneCell = "" Then oneCell.Font.ColorIndex = 1
    Next oneCell
    End With

**Norie** · 11-08-2009, 02:48 PM

Try this.

Code:

Dim oneCell As Range
Dim smallVal As Double

    With ActiveSheet.Range("A1:Z1")
        If Application.CountA(.Cells) >= 4 Then
            smallVal = Application.Small(.Cells, 4)
        Else
            smallVal = Application.Small(.Cells, Application.CountA(.Cells))
        End If

        For Each oneCell In .Cells
            If Val(CStr(oneCell.Value)) <= smallVal Then
                oneCell.Font.ColorIndex = 3
            End If
            If oneCell = "" Then oneCell.Font.ColorIndex = 1
        Next oneCel

**roberto1111** · 11-08-2009, 03:18 PM

Hello Norie,

Thank you for your help.

The code still gives an error when there are only 1,2 or 3 values to process.
I hope you know what the problem is.

**jaslake** · 11-08-2009, 03:31 PM

Hi roberto1111
You could possibly wrap the parameter line of code with an error handler like this

Code:

On Error Resume Next
smallVal4 = Application.Small(.Cells, 4)
On Error GoTo 0

if you're comfortable with what your code is doing.
John

**mikerickson** · 11-08-2009, 04:12 PM

if there are less than 4 values this will set smallVal4 to the largest of those few values.

Code:

With Sheet1.Range("D1:RD1")
    .Offset(0, .Columns.Count).Resize(1, 4).Value = Application.Max(.Cells)
    With .Resize(.Rows.Count, .Columns.Count + 4)
        smallVal4 = Application.Small(.Cells, 4)
    End With
    .Offset(0, .Columns.Count).Resize(1, 4).ClearContents
    MsgBox smallVal4
End With

**Norie** · 11-08-2009, 04:14 PM

Well the code I posted worked for me - no errors and the relevant data was formatted.

Could you please tell us what error you are getting in the first place?

And an explanation of what you are trying to achieve wouldn't go amiss either.