Data_Tbl</SPAN></SPAN> | |||||||
Staff_ID</SPAN></SPAN> | Department 1</SPAN></SPAN> | Department 2</SPAN></SPAN> | Department 3</SPAN></SPAN> | City</SPAN></SPAN> | Date Hired</SPAN></SPAN> | Grade</SPAN></SPAN> | Hotspot Level</SPAN></SPAN> |
ID1</SPAN> | Marketing</SPAN> | Analytics</SPAN> | Core 1</SPAN> | London</SPAN> | 2015</SPAN> | Grade1</SPAN> | |
ID2</SPAN> | Corporate</SPAN> | Modelling</SPAN> | Markets</SPAN> | Pune</SPAN> | 2014</SPAN> | Grade1</SPAN> | |
ID3</SPAN> | Treasury</SPAN> | Regulatory</SPAN> | Africa</SPAN> | Mumbai</SPAN> | 2014</SPAN> | Grade3</SPAN> | |
ID4</SPAN> | Legal</SPAN> | Regulatory</SPAN> | Pacific</SPAN> | London</SPAN> | 2014</SPAN> | Grade4</SPAN> | |
ID5</SPAN> | Legal</SPAN> | Regulatory</SPAN> | London</SPAN> | 2014</SPAN> | Grade4</SPAN> |
<TBODY>
</TBODY>
Hotspot_Tbl</SPAN></SPAN> | |||||
Department 1</SPAN></SPAN> | Department 2</SPAN></SPAN> | Department 3</SPAN></SPAN> | Grade</SPAN></SPAN> | Hotspot Level</SPAN></SPAN> | |
Marketing</SPAN> | Analytics</SPAN> | Core 1</SPAN> | Grade1</SPAN> | Medium</SPAN> | |
Corporate</SPAN> | Modelling</SPAN> | Grade1</SPAN> | High</SPAN> | ||
Marketing</SPAN> | Advertising</SPAN> | Africa</SPAN> | Grade1</SPAN> | Medium</SPAN> | |
Corporate</SPAN> | Services</SPAN> | Green Team</SPAN> | Grade4</SPAN> | High</SPAN> | |
<TBODY>
</TBODY>
- 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>
- 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>