Using arrays as the objects in IF statements...

[firefiend]

okay, I have a doozy of a problem. I'm looking for someone to point me in the right direction, not actually write the code.

essentially, I need this:

If cell.value = {userArray} and cell.offset(0, 2).value = {taskArray}
Then cell.offset(0, 2).interior.colorindex = 3

I have three different {userArray}s and {taskArray}s and there will essentially be different code for each of the arrays and that's fine.

The {userArray} is highly volatile so it needs to be generated from the spreadsheet list to make updates easy. The [taskArray} is less volatile but does change.

SRC Report Inprog-D.xls
	A	B	C	D	E	F
1	userGrp1	taskList1	userGrp2	taskList2	userGrp3	taskList3
2	User1	Task1	User6	Task2	User10	Task3
3	User2	Task2	User7	Task3	User11	Task4
4	User3	Task3	User8	Task6	User12	Task5
5	User4	Task4	User9		User13	Task6
6	User5	Task5			User14
7		Task6			User15
Sheet1

here is the data the arrays should contain.

my problem is setting up and forming the arrays so they can be used within the IF statment.

Can someone give me some tips or reference where I can learn the VBA needed to accomplish this?

 

[Jonmo1]

yeah, now it makes sense. That's the beauty of coding, sometimes the quickest route isn't a straight line, lol.


that's awesome, thanks so much for the help.

Don't worry, I'm quite sure the code I've supplied is NOT the best way to accomplish your goal. I'm still learning too.

 

[firefiend]

Dim retailUsers, retailProcess
retailUsers = Range("U2:U" & Cells(Rows.Count, "U").End(xlUp).Row)
retailProcess = Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row)

here's an interesting twist. I'm the user of this Workbook is requesting that the source ranges for these arrays not be where they are... apparently he does some data manipulation and is prone to deleting to the entire sheet from time to time.

So, I'm trying to move the source ranges to another worksheet but now the arrays aren't filling properly.

I used this code to try an accomplish the task.

Dim retailUsers, retailProcess
retailUsers = Sheets("Dashboard").Range("U2:U" & Cells(Rows.Count, "U").End(xlUp).Row)
retailProcess = Sheets("Dashboard").Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row)

Any ideas what I'm missing?



EDIT:

I've resolved the issues by adding some code to switch active sheets but this like an inefficient way of doing it... anyone know something better?

Sheets("Dashboard").Select
Dim retailUsers, retailProcess
retailUsers = Range("O2:O" & Cells(Rows.Count, "O").End(xlUp).Row)
retailProcess = Range("P2:P" & Cells(Rows.Count, "P").End(xlUp).Row)
Sheets("Time").Select

 

[Jonmo1]

Dim retailUsers, retailProcess 
retailUsers = Sheets("Dashboard").Range("U2:U" & Sheets("Dashboard").Cells(Rows.Count, "U").End(xlUp).Row) 
retailProcess = Sheets("Dashboard").Range("V2:V" & Sheets("Dashboard").Cells(Rows.Count, "V").End(xlUp).Row)