Arrays and IF's

[firefiend]

user = Array("user1", "User2", "User3")
For Each Cell In Range("D2:D" & lastRow)
    If Cell.Value = user Then
        If Cell.Offset(0, -2).Value = "Account Team Reporting" Then
            Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Demand Generation Funds" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Demo Day Process" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Meet Comp Process" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "National Program Support" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "New Product Introductions" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Partner Portal Support" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Meeting" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        ElseIf Cell.Offset(0, -2).Value = "Out of Office" Then Cell.Offset(0, -2).Font.ColorIndex = 1
        Else: Cell.Offset(0, -2).Font.ColorIndex = 3
        End If
    Else: Cell.Offset(0, -2).Font.ColorIndex = 7
    End If
Next Cell

I'm having trouble coding this array. Of course, I never actually used arrays in VBA so that might have something to do with it, lol.

Does anyone have any tips, or resources they can share with me to help solve this?

 

[Norie]

What are you actually trying to do?

This just won't work.

If Cell.Value = user Then

 

[firefiend]

I'm trying to check each cell in range for the users in the array. If one of those users is found in the cell then the script continues. Ignore the Block IF syntax, the code is complete and functional so I didn't include it all.

The crucial part is getting to code to say:

"IF this cell is any of these (points to array) THEN..."


sorry for the confusion.

 

[Norie]

Why not drop the array and use Select Case?

Select Case Cell.Value
    Case "User1", "User2", "User3"
        Select Case Cell.Offset(0, -2).Value
            Case "Account Team Reporting", "Demand Generation Funds", _
                "Demo Day Process", "Meet Comp Process", "National Program Support", _
                "New Product Introductions", "Partner Portal Support", "Meeting", "Out of Office"
                Cell.Offset(0, -2).Font.ColorIndex = 1
            Case Else
                Cell.Offset(0, -2).Font.ColorIndex = 3
        End Select
Case Else
    ' do nothing
End Select

 

[firefiend]

Why not drop the array and use Select Case?

because... uh.... well... when I... uhmm... yeah, I didn't know about that option.

It looks perfect though, I'll start reading up on it. thanks for the help.

 

[Norie]

No problem.

Remember though the code I've posted is untested, it compiles fine though.

It's also pretty static eg the usernames are hardcoded.

If you need it more dynamic it might be worth looking into using arrays.

PS Do you really need code for this anyway?

Wouldn't conditional formatting work?

 

[firefiend]

Arrays would probably be the best solution but CASE would certainly be better than embedded block IF's.

I have about 20-25 (it varies), each in their own user group and each having a different subset.

so, ideally, I would like the arrays to be defined by lists on a spreadsheet and then using those arrays as a variable in the code I posted above. So instead of having code for each user (which would have to be recoded with employee turnover) with an embedded IF, I would have just one bit of code that looks at the usergroup and then cross-indexes it with their subset and then executes the code accordingly.

The dynamics make conditional formatting far too impractical.

I currently have it set up as a user form where the business analyst simply selects the group of users he needs to filter and the code executes, and he can do this for as many user groups at one time as he needs.

My goal is really to make the code as lean and maintainance free as possible and recoding user names and block IF is as talented as I am right now, lol.