Nested for and nested if

Question

I'm trying to implement a nested for and a nested if statement together. I have the following column below. It needs to look at the column if the range is between 500-1000 it should give recommendation a (i.e. write the recommendation in another column) if it is more than 1000 it should give another recommendation in the responding column.

Income Recommendation
550        a
1200       b
750        a
1400       b
600        a

Dim i As Integer
Dim j As Integer


    For i = 2 To Range(i, 1).End(xlDown).Row

        If Cells(i, 1).Value > 1000 Then

        Cells(i, 10).Value = "b"

        i = i + 1

        Else
            If Cells(i, 1).Value < 1000 Then
                If Cells(i, 1).Valie > 500 Then
                Cells(i, 10).Value = "a"
                End If
                End If
        i = i + 1
        End If
    Next i
End Sub

Answer 1

Several errors:

1. Don't rely on i having a value while it is setting the start and end values of the For loop - there is a good chance that it is 0 while calculating Range(i, 1). (Edit: Tested and confirmed that it is still 0 at the point when the end value is being calculated.) Using Range(0, 1) will give a 1004 error.

2. Don't increment the loop counter within the loop (i.e. don't do i = i + 1) - it will almost certainly confuse things. If you really only want to process every second row, use Step 2 on the For statement.

3. .Valie should be .Value

4. Don't use Integer data types for rows - these days Excel can handle 1048576 rows, which is more than an Integer can cope with.

5. Range(1, 1) is invalid syntax. When passing two parameters to the Range property, they need to be cell references. Passing a row and column is what is used when using the Cells property. (So Range(1, 1) will need to be Cells(1, 1), or Range("A1").)

Refactoring your code would give:

    Dim i As Long

    For i = 2 To Cells(1, "A").End(xlDown).Row

        If Cells(i, "A").Value > 1000 Then
            Cells(i, "J").Value = "b"
        ElseIf Cells(i, "A").Value > 500 Then
            Cells(i, "J").Value = "a"
        Else
            Cells(i, "J").Value = ""
        End If
    Next i
End Sub

Answer 2

You can do it like this with Select Case:

Public Sub TestMe()    

    Dim i As Long
    Dim j As Long

    With ActiveSheet
        For i = 2 To .Cells(1, 1).End(xlDown).Row    
            Select Case True    
                Case .Cells(i, 1) > 1000
                    .Cells(i, 10) = "b"    
                Case .Cells(i, 1) < 1000 And .Cells(i, 1) > 500
                    .Cells(i, 10).value = "a"    
            End Select
        Next i
    End With
End Sub

It is more visible and a bit more understandable. Also, make sure that you refer to the Worksheet (in this case with ActiveSheet), to avoid reference problems in the future.