data source1： BH coordination table

data source2：BH layer table

process 1：Collect BH List To Layer Table

Sub FindBHForAllLayers()Dim ws As WorksheetDim wsTarget As WorksheetDim resultRow As LongDim col As Long, targetCol As LongDim lastCol As LongDim layerName As StringSet ws = ThisWorkbook.Sheets("Layer summary")Set wsTarget = ThisWorkbook.ActiveSheetlastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column' 清除之前的结果wsTarget.Range("C2:ZZ50").ClearContents' 遍历每个可能的layer起始列（B,G,L...）For targetCol = 2 To lastCol Step 5layerName = wsTarget.Cells(1, targetCol).ValueIf Not IsEmpty(layerName) ThenresultRow = 2 '每个layer从第2行开始填写' 在Layer summary中搜索这个layerFor col = 1 To lastCol Step 5If Not IsEmpty(ws.Cells(1, col)) Then' 检查该组中的Description列是否包含当前layerDim rng As RangeSet rng = ws.Range(ws.Cells(1, col + 3), ws.Cells(50, col + 3))If Not IsError(Application.Match(layerName, rng, 0)) Then' 写入到layer名称的右边一列wsTarget.Cells(resultRow, targetCol + 1).Value = ws.Cells(1, col).ValueresultRow = resultRow + 1End IfEnd IfNext colEnd IfNext targetCol
End Sub