Using arrays as the objects in IF statements...

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
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
ABCDEF
1userGrp1taskList1userGrp2taskList2userGrp3taskList3
2User1Task1User6Task2User10Task3
3User2Task2User7Task3User11Task4
4User3Task3User8Task6User12Task5
5User4Task4User9User13Task6
6User5Task5User14
7Task6User15
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I would suggest not using arrays in the first place. Your data already appears to be in an excel native array (a range).

You can Use Index based on those ranges
something along the lines of
Code:
Dim usergrp1, taskList1 as Range
Set usergrp1 = Range("A2:A" & Cells(Rows.Count, "A").End(xlup).Row)
Set taskList1 = Range("B2:B" & Cells(Rows.Count, "B").End(xlup).Row)

x = Worksheetfunction.Index(usergrp1,1)
y = Worksheetfunction.Index(taskList1,1)

now you can use x and y for your IF Statement

according to your example
x will = "User 1"
y will = "Task 1"
 
Upvote 0

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
ah, interesting. I didn't realize I could do that. that could work nicely, I think. Thanks for the tip!
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Actually, after answering another post, maybe this works better..

Code:
Dim usergrp1, taskList1
usergrp1 = Range("A2:A" & Cells(Rows.Count, "A").End(xlup).Row) 
taskList1 = Range("B2:B" & Cells(Rows.Count, "B").End(xlup).Row) 

x = usergrp1(1,1)
y = taskLlist1(1,1)

The array is a 2D array, you need to specify 2 dimensions (Row, Column)
 
Upvote 0

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
Thanks for the help, Jonmo.

Your suggestion has certainly made the code easier to handle. But I have run into a slight snag.

Code:
Sub retailScrub()

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)
users = retailUsers(1, 1)
process = retailProcess(1, 1)

For Each Cell In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    If Cell.Value = users Then
        If Cell.Offset(0, -2).Value = process 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 = 1
    End If
Next Cell

End Sub

The code doesn't check each of the users and processes in the userGrp1 and taskList1 list.

It just checks the first entries on those lists. Is there a way to have it cycle through each? So that when it sees 'users' it looks for anyone on the userGrp1 list and the same for "process"?
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this
Code:
Sub retailScrub() 

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) 

For Each Cell In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row) 
    on error resume next
    x = ""
    y = ""
    x = WorksheetFunction.Match(Cell.Value, retailUsers, 0)
    y = WorksheetFunction.Match(Cell.Offset(0, -2).Value, retailProcess, 0)
    If x <> "" Then 
        If y <> "" 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 = 1 
    End If 
Next Cell 

End Sub
 
Upvote 0

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
That worked! Thank you, thank you.

I don't know why it worked but it did, lol.
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I don't know why it worked but it did
Code:
    x = "" 
    y = "" 
    x = WorksheetFunction.Match(Cell.Value, retailUsers, 0) 
    y = WorksheetFunction.Match(Cell.Offset(0, -2).Value, retailProcess, 0)

The match function looks for the Cell Value in the retailusers array.
If it's found, X will = the position # where it was found in the array.
If it is not found, x will = ""

since those lines are in a loop, x and y will be reset to "" each loop.

the on error resume next makes the code NOT error when the value is NOT found in the array.

Does that help?
 
Upvote 0

firefiend

Board Regular
Joined
Feb 12, 2007
Messages
74
I'm starting to get it.

I'm a bit thrown off by using a <> expression instead an ='s expression.


EDIT: just read your PM, it makes much more sense now!
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
<> means "Does Not Equal"
so IF the variable x = ANYTHING other than "" THEN ...

If the Cell.Value IS FOUND in the retailUsers array, x will = the position # where it was found in the array. And the IF statement will result in TRUE.

If the Cell.Value is NOT found in the array, then x will remain = "". and the IF statement will result in FALSE.
 
Upvote 0

Forum statistics

Threads
1,191,400
Messages
5,986,368
Members
440,021
Latest member
Virtual_Candy_84

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