• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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
0
Modifier1000
Asked:
Modifier1000
  • 4
  • 2
  • 2
  • +1
5 Solutions
 
NorieCommented:
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

0
 
lwadwellCommented:
You are not far off .. if the first empty color cell was A2 ... making the play value C2 (adjust for your case).  In A2 put the formula
=if(C2="Sweep","Blue",if(C2="Dive","Red",if(C2="Toss","Green",if(C2="Slant","White",if(C2="Keeper","Gold","")))))
and copy down the column.
0
 
sungenwangCommented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NorieCommented:
If you want a formula:

=LOOKUP(A1, {"Sweep";"Dive";"Toss";"Slant";"Keeper"}, {"Blue";"Red";"Green";"White";"Gold"})

Enter this in the cell next to Sweep/Dive etc, change the cell reference A1 to that cell's reference and copy down.
0
 
NorieCommented:
Here's my code updated to reflect the columns in the image.
Dim I As Long
Dim cellplay As Range
Dim cellcolor As String

    For I = 1 To 10

        Set cellplay = Range("C" & 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(, -2).Value = cellcolor

    Next I

Open in new window

0
 
Modifier1000Author Commented:
Ok, here is a DUMB question in regards to VBA.  I code in PHP, Javascript and such.  But how do I run this code in VBA?

I'm not a VBA person at all.
0
 
sungenwangCommented:
Hit Alt-F8, type a new macro name and select Create. Paste the code to the subroutine.
To run macro from your worksheet, hit Alt-F8, select the macro you created and click Run.
sew
0
 
NorieCommented:
Or if you want the code to run automatically whenever a value changes in a specified range, eg C2:C11 you could use a worksheet change event.

To use this code right click the tab of the worksheet you have the data on, select View Code... and paste the code in the code window.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cellcolor As String
Dim cellplay As Range

    If Intersect(Range("C2:C11"), Target) Is Nothing Then Exit Sub
    
    If Target.Cells.Count > 1 Then Exit Sub

        Set cellplay = Target
        
        Select Case cellplay.Value

            Case "Sweep"

                cellcolor = "Blue"

            Case "Dive"

                cellcolor = "Red"

            Case "Toss"

                cellcolor = "Green"

            Case "Slant"

                cellcolor = "White"

            Case "Keeper"

                cellcolor = "Gold"

        End Select

        Application.EnableEvents = False
        cellplay.Offset(, -2).Value = cellcolor
        Application.EnableEvents = True

End Sub

Open in new window

By the way, you've not actually mentioned what range(s) are involved here.

I've 'guessed' C2:C11, is that right?
0
 
Modifier1000Author Commented:
Thanks to al!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now