VBA Help - Cycle through reporting hierarchy to find FIRST MATCH

[Nexus981]

Hi,

I am working with a old macro written by someone no longer around (and secondly its not working consistently)

I have a reporting Hierarchy that looks like this
[TABLE="width: 519"]
<tbody>[TR]
[TD="align: right"]18615
[/TD]
[TD],38630,29199,22163,26079,14516,16114,10614
[/TD]
[/TR]
</tbody>[/TABLE]
I removed the commas and have each ID in its own cell going from Left to right.

I have a separate list of IDS where I would like the matching to stop once it finds the FIRST match. I.E. I want to stop when I find an employee that is the FIRST VP in this person's hierarchy.

[TABLE="width: 519"]
<tbody>[TR]
[TD="align: right"]18615
[/TD]
[TD],38630,29199,22163,26079,14516,16114,10614
[/TD]
[/TR]
</tbody>[/TABLE]
n/a n/a n/a YES

From there I will look for this ID and bring back the details (I think I can handle that part of the code)

I need to run through this process roughly 3,000+ times

 

[Nexus981]

... more detail

See bellow ... I would have 3K+ rows of data that look like the below
[TABLE="width: 1055"]
<tbody>[TR]
[TD]emp_id[/TD]
[TD]emp_id_chain[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 3[/TD]
[TD]Level 4[/TD]
[TD]Level 5[/TD]
[TD]Level 6[/TD]
[TD]Level 7[/TD]
[TD]Level 8[/TD]
[/TR]
[TR]
[TD="align: right"]18615[/TD]
[TD],38630,29199,22163,26079,14516,16114,10614[/TD]
[TD="align: right"]38630[/TD]
[TD="align: right"]29199[/TD]
[TD="align: right"]22163[/TD]
[TD="align: right"]26079[/TD]
[TD="align: right"]14516[/TD]
[TD="align: right"]16114[/TD]
[TD="align: right"]10614[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]36627[/TD]
[TD],39982,30821,12573,12060,17853,16114,10614[/TD]
[TD="align: right"]39982[/TD]
[TD="align: right"]30821[/TD]
[TD="align: right"]12573[/TD]
[TD="align: right"]12060[/TD]
[TD="align: right"]17853[/TD]
[TD="align: right"]16114[/TD]
[TD="align: right"]10614[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5495[/TD]
[TD],4194,4196,4582,4581,16114,10614[/TD]
[TD="align: right"]4194[/TD]
[TD="align: right"]4196[/TD]
[TD="align: right"]4582[/TD]
[TD="align: right"]4581[/TD]
[TD="align: right"]16114[/TD]
[TD="align: right"]10614[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="7"></colgroup>[/TABLE]

 

[tonyyy]

Nexus981,

Welcome to the Board.

I want to stop when I find an employee that is the FIRST VP in this person's hierarchy.

Given your range of id numbers, how are we to determine which is a VP? (Maybe you have a separate table or list of vp id's? On the same sheet? In what range?)

Once found, how do you want the results displayed? Highlighted? Added to another column? (Which column?)

Cheers,

tonyyy

 

[Nexus981]

Tonny,

1. I have the list of VPs on a different excel tab
2. As far as output goes, I would place the resulting FIRST MATCH next to the unique EMP_ID ( i.e. column 11, row X)


FIND FIRST MATCH -> Look up said match -> Return Results ( I would be bringing in other values like the name ,locations etc. ALL based of FIRST MATCH)

Thanks for your help so far.

 

[tonyyy]

You might consider the following...

Sub FirstMatch_1023533()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant
Dim r As Long, c As Long, i As Long

Set ws1 = Sheets("Sheet1") 'employee list
Set ws2 = Sheets("Sheet2") 'vp list

arr1 = ws1.Range(ws1.Cells(2, 1), ws1.Cells(ws1.Cells(Rows.Count, 1).End(xlUp).Row, 10)) 'emp array
arr2 = ws2.Range(ws2.Cells(1, 1), ws2.Cells(ws2.Cells(Rows.Count, 1).End(xlUp).Row, 1)) 'vp array
ReDim arr3(1 To Cells(Rows.Count, 1).End(xlUp).Row - 1, 1 To 1) 'results array

For r = 1 To UBound(arr1, 1)
    For c = 3 To UBound(arr1, 2)
        For i = 1 To UBound(arr2, 1)
            If arr1(r, c) = arr2(i, 1) Then
                arr3(r, 1) = arr2(i, 1)
                GoTo Nexxt
            End If
        Next i
    Next c
Nexxt:
Next r

ws1.Range("K2:K" & ws1.Cells(Rows.Count, 1).End(xlUp).Row).Value = arr3
End Sub

Assumptions:

The employee list is on "Sheet1"
The vp id numbers are on "Sheet2" in Column A

 

[Nexus981]

I will check this out. Thanks for all the help. I need to get better at this stuff. While I was waiting I banged it out in excel with embedded If error statements. It got the job done, but I need to get better at VBA, so powerful.

 

[tonyyy]

You're welcome. Glad you have a solution...