Loop through array - looking up multiple columns

[trux101]

[TABLE="width: 584, align: center"]
<TBODY>[TR]
[TD="colspan: 8"]Data_Tbl</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff_ID</SPAN></SPAN>
[/TD]
[TD]Department 1</SPAN></SPAN>
[/TD]
[TD]Department 2</SPAN></SPAN>
[/TD]
[TD]Department 3</SPAN></SPAN>
[/TD]
[TD]City</SPAN></SPAN>
[/TD]
[TD]Date Hired</SPAN></SPAN>
[/TD]
[TD]Grade</SPAN></SPAN>
[/TD]
[TD]Hotspot Level</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]ID1</SPAN>
[/TD]
[TD]Marketing</SPAN>
[/TD]
[TD]Analytics</SPAN>
[/TD]
[TD]Core 1</SPAN>
[/TD]
[TD]London</SPAN>
[/TD]
[TD]2015</SPAN>
[/TD]
[TD]Grade1</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID2</SPAN>
[/TD]
[TD]Corporate</SPAN>
[/TD]
[TD]Modelling</SPAN>
[/TD]
[TD]Markets</SPAN>
[/TD]
[TD]Pune</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[TD]Grade1</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID3</SPAN>
[/TD]
[TD]Treasury</SPAN>
[/TD]
[TD]Regulatory</SPAN>
[/TD]
[TD]Africa</SPAN>
[/TD]
[TD]Mumbai</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[TD]Grade3</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID4</SPAN>
[/TD]
[TD]Legal</SPAN>
[/TD]
[TD]Regulatory</SPAN>
[/TD]
[TD]Pacific</SPAN>
[/TD]
[TD]London</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[TD]Grade4</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID5</SPAN>
[/TD]
[TD]Legal</SPAN>
[/TD]
[TD]Regulatory</SPAN>
[/TD]
[TD][/TD]
[TD]London</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[TD]Grade4</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="align: center"]
<TBODY>[TR]
[TD="colspan: 5"]Hotspot_Tbl</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Department 1</SPAN></SPAN>
[/TD]
[TD]Department 2</SPAN></SPAN>
[/TD]
[TD]Department 3</SPAN></SPAN>
[/TD]
[TD]Grade</SPAN></SPAN>
[/TD]
[TD="colspan: 2"]Hotspot Level</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Marketing</SPAN>
[/TD]
[TD]Analytics</SPAN>
[/TD]
[TD]Core 1</SPAN>
[/TD]
[TD]Grade1</SPAN>
[/TD]
[TD="colspan: 2"]Medium</SPAN>
[/TD]
[/TR]
[TR]
[TD]Corporate</SPAN>
[/TD]
[TD]Modelling</SPAN>
[/TD]
[TD][/TD]
[TD]Grade1</SPAN>
[/TD]
[TD="colspan: 2"]High</SPAN>
[/TD]
[/TR]
[TR]
[TD]Marketing</SPAN>
[/TD]
[TD]Advertising</SPAN>
[/TD]
[TD]Africa</SPAN>
[/TD]
[TD]Grade1</SPAN>
[/TD]
[TD="colspan: 2"]Medium</SPAN>
[/TD]
[/TR]
[TR]
[TD]Corporate</SPAN>
[/TD]
[TD]Services</SPAN>
[/TD]
[TD]Green Team</SPAN>
[/TD]
[TD]Grade4</SPAN>
[/TD]
[TD="colspan: 2"]High</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

1. The macro needs to loop through each line on the Data_Tbl with the ultimate aim of returning the Hotspot Level. I could create a unique key using concatenated data but that is far from ideal as I’d have to do similar lookups for other data using the same approach but with other data.</SPAN>

1. So to find the hotspot level of each individual the macro has to loop and check if the Hotspot_Tbl has any ‘hits’ for Department 1, Department 2, Department 3 and Grade. Some individuals may not have a Department 3 and the macro should be able to work with or without Department 3. </SPAN>

In the example above – Data_Tbl the hotspot level for employee ID1 would be Medium and ID2 will be High – the rest will be blanks as there aren’t any levels for them in the Hotspot_Tbl.</SPAN>

Below is my current code and it isn’t looping correctly…It returns Medium for everyone!</SPAN>


Public Function HotSpots_Test()</SPAN>
Dim Staff_Id As Variant</SPAN>
Dim Input_Lookup As Variant</SPAN>
Dim Ln As Integer</SPAN>
Dim Hotspot_Tbl As Variant</SPAN>
Dim Hotspot_Level As Variant</SPAN>
Dim Data_Tbl As Variant</SPAN>

Hotspot_Tbl = Range("Hotspots")</SPAN>
Staff_Id = Range("Data_Tbl[Staff ID]")</SPAN>
Input_Lookup = Range("Data_Tbl")</SPAN>

ReDim Hotspot_Level(1 To UBound(Staff_Id), 1 To 1)</SPAN>
For Ln = 1 To UBound(Staff_Id)</SPAN>

If Input_Lookup(Ln, 2) = Hotspot_Tbl(Ln, 1) Then</SPAN>
If Input_Lookup(Ln, 3) = Hotspot_Tbl(Ln, 2) Then</SPAN>
If Input_Lookup(Ln, 4) = Hotspot_Tbl(Ln, 3) Then</SPAN>
If Input_Lookup(Ln, 5) = Hotspot_Tbl(Ln, 4) Then</SPAN>

Hotspot_Level = Hotspot_Tbl(Ln, 7)</SPAN>

Else</SPAN>

Hotspot_Level = "Blank"</SPAN>

End If</SPAN>
End If</SPAN>
End If</SPAN>
End If</SPAN>

Next Ln</SPAN>

Range("Data_Tbl[CY Hotspot Level]") = Hotspot_Level</SPAN>

End Function</SPAN>

 

[ButtFace]

Thoroughly confused.
Say I added a line to your Data_Tbl: ID6, Corporate, Services, , Mesopotamia, 3700BC, Grade 4
Should it return a result of "High" based on the last line of your Hotspot_Tbl?
You're trying to return the Hotspot level of the row that matches Dept1, Dept2, and Grade?

 

[trux101]

Hi, thanks for replying.
Should it return a result of "High" based on the last line of your Hotspot_Tbl? Correct
You're trying to return the Hotspot level of the row that matches Dept1, Dept2, and Grade? Correct but the code should match Dept1, Dept2, Dept and Grade first if none match then Dept1, Dept2 and Grade

 

[RickXL]

Hi,

You have a couple of mistakes.

1. This line is comparing a City with a Grade.

If Input_Lookup(Ln, 5) = Hotspot_Tbl(Ln, 4) Then

2. You actually need two loops not just one. The loops need to be one inside the other so it steps to line 1 of the Data Table then loops through all the lines in the Hotspots Table etc.

Regards,