Error with If/Else macro code

[Noobette]

I've got an error here, and I need to modify it to perform better.

My data looks like this in column A.

apple

apple

Orange

Orange

apple

pear

apple

carrot

Orange

pear

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

Sub WorkSheet_Fruit()
For i = 1 To 10
yum = Sheet1.Cells(i, 2).Value
If yum = "apple" Then
Sheet1.Cells(i, 3).Value = "a"
ElseIf yum = "orange" Then
Sheet1.Cells(i, 3).Value = "o"
ElseIf yum < "pear" Then
Sheet1.Cells(i, 3).Value = "p"
Else
Sheet1.Cells(i, 3).Value = "veggie"
End If
Next i
End Sub

The error is that the code returns "p" in each of the 10 cells in column C.
I'd like to modify it to be flexible with a variable number of rows in Column A.

Any help is appreciated!

 

[drsarao]

Try this:

Sub WorkSheet_Fruit()
lr = Cells(Rows.Count, "A").End(xlUp).Row 'get last filled row
For i = 1 To lr
    Select Case Sheet1.Cells(i, 1).Value 'col 1 instead of 2!
        Case "apple"
            Sheet1.Cells(i, 3).Value = "a"
        Case "orange"
            Sheet1.Cells(i, 3).Value = "o"
        Case Is < "pear"
            Sheet1.Cells(i, 3).Value = "p"
        Case Else
            Sheet1.Cells(i, 3).Value = "veggie"
    End Select
Next i
End Sub

 

[Noobette]

Ah, drsarao, worked like a charm. Thank you!

 

[drsarao]

You are welcome!