CODEWITHSUNDEEP
excelvba
watapana
watapana

Reputation: 1

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

I'm trying to speed up a process where a macro checks a range of text in column B to group them in column C as a specific keyword. For example, if B2 has apple, it marks it as fruit in C2, if B3 has Onion, it marks it as vegetables in C3. Eventually some other texts I would like for them to just appear as "other". Unfortunately, I am not having any luck in actually making it work as I wish.

Sub Categorize()

If Range("B2:B100").text="Apple" then
Range("C2:C100").text="Fruit"

ElseIf Range("B2:B100").text="Banana" Then
Range("C2:C100").text="Fruit"

ElseIf Range("B2:B100").text="Onion" Then
Range("C2:C100").text="Vegetable"

Else
Range("C2:C100")="Other"

End If
End Sub

Can you guys help me out?

Upvotes: 0

Views: 3175

Answers (3)

Galust
Galust

Reputation: 1

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

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9932

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"")"

Upvotes: 3

PaichengWu
PaichengWu

Reputation: 2689

If you care about speed, use Excel Formula. enter image description here

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"")"

enter image description here

Upvotes: 1

Related Questions