If logic to write one of three texts into a cell

Question

I would like to write text a, b or c in E3 depending on the result.

The code crashes Excel.

Sub calculate()
Range("B2").Activate
Do While ActiveCell.Value <> ""
    If ActiveCell.Value + ActiveCell.Offset(0, 1) + ActiveCell.Offset(0, 2) = 3 Then
        ActiveCell.Offset(0, 3).Value = "Text A"
    ElseIf ActiveCell.Value + ActiveCell.Offset(0, 1) + ActiveCell.Offset(0, 2) = 2 Then
        ActiveCell.Offset(0, 3).Value = "Text B"
    ElseIf ActiveCell.Value + ActiveCell.Offset(0, 1) + ActiveCell.Offset(0, 2) < 2 Then
        ActiveCell.Offset(0, 3).Value = "Text C"
    End If
Loop
End Sub

Do you intend to move down the column or just examine **B3** over and over again ?? — Gary's Student, Jan 25 '20 at 16:04
I've edited the code. I would like to write text a, b or c in E3 depending on the result — Heroo, Jan 25 '20 at 16:26
Looks to me like your Do While .. Loop should just be an If .. Endif block. Unless the ActiveCell value is changed inside the loop it will never exit. — CDP1802, Jan 25 '20 at 17:17
**1.** Find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) **2.** Use a `FOR` loop to loop through the cells in that column. If you use a `Do While ActiveCell.Value <> ""` then the code will stop mid way if there is a blank cell in between. **3.** Use a `Select Case` instead of `If-EndIf`. It is much easier to read and maintain. **NOTE** These are just my suggestions and not a hard and fast rule to follow. — Siddharth Rout, Jan 25 '20 at 17:46

score 1 · Accepted Answer · answered Jan 25 '20 at 17:25

Use some variables! Maybe something like this:

Sub calculate()
    Dim c As Range, v
    Set c = ActiveSheet.Range("B2") 

    Do While c.Value <> ""
        v = c.Value + c.Offset(0, 1).Value + c.Offset(0, 2).Value
        If v = 3 Then
            c.Offset(0, 3).Value = "Text A"
        ElseIf v = 2 Then
            c.Offset(0, 3).Value = "Text B"
        ElseIf v < 2 Then
            c.Offset(0, 3).Value = "Text C"
        End If
        Set c = c.offset(1, 0)
    Loop

End Sub

If logic to write one of three texts into a cell

1 Answers1