VBA Macro for coding mulpiple cases

[halcyon]

Hi, I'm trying to write a macro (below) that will add an appropriate range to a column. There are about 8 different cases and each case has about 6 distinct salary ranges (different from each case). The macro below is my attempt at a 'test', when run an a quickly made sheet though it dosen't quite work. I *think* the problem is in the logic between the For and IF statements. Can anyone give me a hand with this?

Cheers.



Dim ROWCOUNT As Integer

ROWCOUNT = Application.CountA(ActiveSheet.Range("A:A"))
For X = 2 To ROWCOUNT
If Cells(X, 2) = "CSR" Then Cells(X, 4).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]>=1,RC[-1]<=5),""1-5"",IF(AND(RC[-1]>=6,RC[-1]<=10),""6-10"",""Check""))"
Next
For X = 2 To ROWCOUNT
If Cells(X, 2) = "CM" Then Cells(X, 4).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]>=1,RC[-1]<=10),""1-10"",IF(AND(RC[-1]>=11,RC[-1]<=20),""11-20"",""Check""))"
Next
End Sub

 

[Barrie Davidson]

Try this (and just add more CASE IS statements):

Dim ROWCOUNT As Integer

ROWCOUNT = Application.CountA(ActiveSheet.Range("A:A"))
For X = 2 To ROWCOUNT
    Select Case Cells(X, 2).Value
    Case Is = "CSR"
        Cells(X, 4).FormulaR1C1 = _
            "=IF(AND(RC[-1]>=1,RC[-1]<=5),""1-5"",IF(AND(RC[-1]>=6,RC[-1]<=10),""6-10"",""Check""))"
    Case Is = "CM"
        Cells(X, 4).FormulaR1C1 = _
            "=IF(AND(RC[-1]>=1,RC[-1]<=10),""1-10"",IF(AND(RC[-1]>=11,RC[-1]<=20),""11-20"",""Check""))"
    Case Else
    End Select
Next X

Let me know if you need any further help.