How to use string-text within If-else statements in Excel VBA

This should do what you want. Keep an eye out for caps issues, or just force everything to be upper/lower case.

Sub Categorize()
Dim aCell As Range
Const theColumnToWriteTo As Long = 4 'column d    

For Each aCell In Range("b2:b100").Cells


If aCell.Value2 = "Apple" Then

    aCell.Worksheet.Cells(aCell.Row, theColumnToWriteTo).Value = "Fruit"

ElseIf aCell.Value2 = "Banana" Then
    aCell.Worksheet.Cells(aCell.Row, theColumnToWriteTo).Value = "Fruit"

ElseIf aCell.Value2 = "Onion" Then
    aCell.Worksheet.Cells(aCell.Row, theColumnToWriteTo).Value = "Vegetable"

Else
    aCell.Worksheet.Cells(aCell.Row, theColumnToWriteTo).Value = "other"

End If

Next aCell

End Sub

UPDATE Here's an alternative approach that is more complex, but is FAR more efficient and really is the "correct" way to do such actions. The result is that your worksheet is only modified once, which can make a huge difference when doing thousands of cells in a worksheet with formulas everywhere.

You can change the Const parameters.

Sub getArays()
Const pullRangeAddress As String = "B2:B100"
Const destinationAddress As String = "C2"
Dim WS As Worksheet: Set WS = ActiveSheet ' of whatever sheet



    Dim tRay(): tRay() = WS.Range(pullRangeAddress).Value2

    'create new blank array to hold values
    ReDim nRay(LBound(tRay, 1) To UBound(tRay, 1), LBound(tRay, 2) To UBound(tRay, 2))


    Dim x As Long, y As Long
    For x = LBound(tRay, 1) To UBound(tRay, 1)
        For y = LBound(tRay, 2) To UBound(tRay, 2)

            If tRay(x, y) = "Banana" Then
                nRay(x, y) = "Fruit"

            ElseIf tRay(x, y) = "Apple" Then
                nRay(x, y) = "Fruit"

            ElseIf tRay(x, y) = "Onion" Then
                nRay(x, y) = "Vegetable"

            Else
                nRay(x, y) = "Other"

            End If
        Next y
    Next x


    WS.Range(destinationAddress).Resize(UBound(nRay, 1), UBound(nRay, 2)) = nRay

End Sub

Updated Again Trying To Keep Everyone In The Comments Happy

You could use a select statement which is a little easier to visualize.

For x = LBound(tRay, 1) To UBound(tRay, 1)

For y = LBound(tRay, 2) To UBound(tRay, 2)

Select Case tRay(x, y)

    Case "Banana", "Apple", "Grapes"
        nRay(x, y) = "Fruit"

    Case "Onion"
        nRay(x, y) = "Vegetable"

    Case "Mushrooms", "Weed"
        nRay(x, y) = "illegal"

    Case Else
        nRay(x, y) = "Other"

    End Select
Next y
Next x

Adding Another Option That Leverages Excel's new IFS function... Different answer had a good idea of just using a formula. I like the concept, but no helper column allowed!

Range("D2:D200").FormulaR1C1 _
"=IFS(OR(RC[-1]=""Apple"",RC[-1]=""Banana""),""Fruit"",RC[-1]=""Onion"",""Vegetable"",TRUE,""Other"")"

If you care about speed, use Excel Formula.

If you want to use VBA, you could use FormulaR1C1 property of Range object.

Range("D2:D200").FormulaR1C1 = "=iferror(vlookup(rc2,c7:c8,2,false),""other"")"

Sub UpdateScale()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long

On Error GoTo ScalingProblem
    'Assigns the values in the Min and Max ranges to variables.
    With Ëèñò9
    ' Sheet9
        lMax = .Range("Max").Value
        lMin = .Range("Min").Value
        'Creates chart object.
        Set ChartVar = .ChartObjects("Chart 1").Chart
        
               With ChartVar.Axes(xlValue, xlPrimary)  'Adjusts the price axis
                   .MinimumScale = 1.301    'iMin
                   .MaximumScale = 1.326    'iMax
               End With
            
    End With
Exit Sub

ScalingProblem:
'RetrievalProblem:
'    MsgBox "Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error"
End Sub