Select Case Question

[CYoung]

I have different rates that I'd like to assign to different values. For example:

Code Rate
Code 1 Rate 1
Code 2 Rate 1
Code 3 Rate 2
Code 4 Rate 2
Code 5 Rate 1
Code 6 Rate 1
Code 7 Rate 2
Code 8 Rate 1
Code 9 Rate 1
Code 10 Rate 1

I have a string Variable that I set equal to all of the codes that should be assigned rate 1 and another variable of all the codes that should be assigned rate 2, using double quotes around the names and commas to separate them.

The results would look like:
Variable1 = "Code 1", "Code 2", Code 5", "Code 6", Code 8", "Code 9", "Code 10"
Variable2 = "Code 3", "Code 4", Code 7"

Later, I try to do a Select Case statement using the variables to then assign the rates as the loop moves through the rows.

Select Case ActiveCell.Offset(0, 5)
Case Variable1
ActiveCell.Value = Rate 1
Case Variable 2
ActiveCell.Value = Rate 2


It doesn't seem to recognize the strings Variable1 & Variable2 in assigning the cases. Any thoughts?

 

[MrKowz]

You can't use a variable in that manner. Instead, try this:

Dim variable1 As Variant
Dim variable2 As Variant
 
variable1 = Array("Code 1", "Code 2", "Code 5", "Code 6", "Code 8", "Code 9", "Code 10")
variable2 = Array("Code 3", "Code 4", "Code 7")
 
If Not IsError(Application.Match(ActiveCell.Offset(0, 5).Value, variable1, 0)) Then
    ActiveCell.Value = Rate1
ElseIf Not IsError(Application.Match(ActiveCell.Offset(0.5).Value, variable2, 0)) Then
    ActiveCell.Value = Rage2
End if

 

[CYoung]

that was fast!

Thanks for the idea. i'll give it a shot and let ya know.

 

[Rasm]

Try this - you are missing quotes as well - next time use the code markers

Select Case ActiveCell.Offset(0, 5)
        Case "Code 1", "Code 2", "Code 5", "Code 6", "Code 8", "Code 9", "Code 10"
            ActiveCell.Value = "Rate 1"
        Case "Code 3", "Code 4", "Code 7"
            ActiveCell.Value = "Rate 2"
        Case Else
            MsgBox "No value found"
 End Select

 

[CYoung]

can i use a variable name inside the array?

basically, i loop through about 100 rows to determine which ones below to Variable1 or Variable 2, and it could change. so i need to flexibility to be able to determine what goes in that.

 

[MrKowz]

Something like this?

Public Sub TestMe()
Dim rng         As Range, _
    variable1   As Variant, _
    variable2   As Variant
    
variable1 = Array("Code 1", "Code 2", "Code 5", "Code 6", "Code 8", "Code 9", "Code 10")
variable2 = Array("Code 3", "Code 4", "Code 7")

For Each rng In Range("A1:A100")
    If Not IsError(Application.Match(rng.Offset(0, 5).Value, variable1, 0)) Then
        rng.Value = Rate1
    ElseIf Not IsError(Application.Match(rng.Offset(0.5).Value, variable2, 0)) Then
        rng.Value = Rage2
    End If
Next rng
End Sub