Select Case for Columns

[bull sprig]

New to Excel VB, so don't know the syntax for what I am trying to do. Any help is appreciated! I'm sure this is completely obvious and simple for someone...

What I have is a list in C7:C100. Depending on what is chosen in this column, I want a different value in E7:E1000. I am trying to use a select case for this. Obviously is works for C7 and E7, but how do I get it to work for the remaining cells in the columns? Here is the code I have so far:

Select Case Range("C7").Text
 
            Case "Text 1:"
            Range("E7").Value = 10
 
            Case "Text 2:"
            Range("E7").Value = 15   
 
            Case "Text 3:"
            Range("E7").Value = 20
 
            Case Else
            Range("E7").Value = ""
 
 End Select

Thank you!

 

[VoG]

Hello and welcome.What exactly do you mean by

Depending on what is chosen in this column

?

 

[bull sprig]

So in Column C7:C1000, the user is able to select from a drop down list which fills that cell with text. Based on that selection, I want the corresponding value to be pre-filled in column E7:E1000. So if the user selects a certain text value (say "Text 1") in cell C10, then I want cell E10 to prefill to 10 and so on for C11/E11, C12/E12, ....

 

[wigi]

Like this:

Sub LoopThroughCells()

    Dim rng As Range

    For Each rng In Range("C7:C1000")

        Select Case rng.Text

        Case "Text 1:": rng.Offset(, 2).Value = 10
        Case "Text 2:": rng.Offset(, 2).Value = 15
        Case "Text 3:": rng.Offset(, 2).Value = 20
        Case Else: rng.Offset(, 2).Value = ""

        End Select
        
    Next

End Sub

or:

Sub LoopThroughCells()

    Dim rng As Range

    For Each rng In Range("C7:C1000")

        Select Case rng.Text

        Case "Text 1:": Range("E" & rng.Row).Value = 10
        Case "Text 2:": Range("E" & rng.Row).Value = 15
        Case "Text 3:": Range("E" & rng.Row).Value = 20
        Case Else: Range("E" & rng.Row).Value = ""

        End Select
        
    Next

End Sub

 

[bull sprig]

Awesome. Thank you!

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C7:C100")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Text
        Case "Text 1:": Target.Offset(, 2).Value = 10
        Case "Text 2:": Target.Offset(, 2).Value = 15
        Case "Text 3:": Target.Offset(, 2).Value = 20
        Case Else: Target.Offset(, 2).Value = ""
    End Select
    Application.EnableEvents = True
End If
End Sub