Select Case Question

CYoung

New Member
Joined
Jan 5, 2009
Messages
6
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You can't use a variable in that manner. Instead, try this:

Code:
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
 
Last edited:

CYoung

New Member
Joined
Jan 5, 2009
Messages
6
that was fast!

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

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
Try this - you are missing quotes as well - next time use the code markers

Code:
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

New Member
Joined
Jan 5, 2009
Messages
6
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Something like this?

Code:
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
 

Forum statistics

Threads
1,141,844
Messages
5,708,928
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top