Arrays and IF's

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
Code:
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What are you actually trying to do?

This just won't work.
Code:
If Cell.Value = user Then
 

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Why not drop the array and use Select Case?
Code:
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

Board Regular
Joined
Feb 12, 2007
Messages
74
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 12, 2007
Messages
74
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.
 

Forum statistics

Threads
1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

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