asked on

Conditional statement to fill in cells?

Hello Experts,

I have a group of terms that need to be plugged in to specific cells based on text from another cell. (Image Attached)

I think the image would make more sense than my trying to explain it. But the pseudocode could be something like the following or probably a switch statement.

if(cellPlay=”Sweep”)
{
  cellColor=”Blue”;
}
else if (cellPlay=”Dive”)
{
   cellColor=”Red”);
}
else if(cellPlay=”Toss”)
{
   cellColor=”Green”);
}
else if(cellPlay=”Slant”)
{
   cellColor=”White”;
}
else if(cellPlay=”Keeper”)
{
   cellColor=”Gold”
}

Open in new window

if-else.png

Norie

There's no switch statement in Excel but what you could use is a Select Case.

This code will go down A1:A10, check the value in each cell and put the appropriate term in the cell to the right.

Dim I As Long
Dim cellplay As Range
Dim cellcolor As String

    For I = 1 To 10

        Set cellplay = Range("A" & I).Value

        cellcolor = ""

        Select Case cellplay

            Case "Sweep"

                cellcolor = "Blue"

            Case cellplay = "Dive"

                cellcolor = "Red"

            Case "Toss"

                cellcolor = "Green"

            Case "Slant"

                cellcolor = "White"

            Case "Keeper"

                cellcolor = "Gold"

        End Select

        cellplay.Offset(, 1).Value = cellcolor

    Next I

Open in new window

SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sungenwang

Here's a VBA that uses Select Case to insert the value. Note that I assume the header line starts on row 1. 'Color' is on column A and 'Play' is on column C.
sew

Sub InsertColorText()
    Dim intSrcRow As Integer
    Dim strColumn As String
    Dim strData As String

    strColumn = "C"

    For intSrcRow = 2 To ActiveSheet.Cells(Cells.Rows.Count, strColumn).End(xlUp).Row
        strData = ActiveSheet.Cells(intSrcRow, strColumn).Value
        
        Select Case strData
            Case "Sweep":
                ActiveSheet.Cells(intSrcRow, "A").Value = "Blue"
            Case "Dive":
                ActiveSheet.Cells(intSrcRow, "A").Value = "Red"
            Case "Toss":
                ActiveSheet.Cells(intSrcRow, "A").Value = "Green"
            Case "Slant":
                ActiveSheet.Cells(intSrcRow, "A").Value = "White"
            Case "Keeper":
                ActiveSheet.Cells(intSrcRow, "A").Value = "Gold"
        End Select
    Next
End Sub

Open in new window

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.