|
发表于 2014-8-12 18:01
|
显示全部楼层
本楼为最佳答案
- Sub search()
- Dim v$, arr, i&, j%, irow&, icol%, re(), st&, c%
- v = ActiveCell.Value
- If Len(Trim(v)) = 0 Then MsgBox "请选择一个正确的人名": Exit Sub
- irow = ActiveCell.Row
- icol = ActiveCell.Column
- arr = Range("A1").CurrentRegion.Value
- ReDim re(1 To 10000, 1 To UBound(arr, 2))
- '上几代的人名
- st = irow
- For j = icol To 1 Step -1
- For i = st To 2 Step -1
- If arr(i, j) <> "" Then
- re(j, j) = arr(i, j)
- st = i - 1
- Exit For
- End If
- Next
- Next
- '后代的人名
- st = icol
- For i = irow + 1 To UBound(arr)
- If arr(i, icol) = "" Then
- c = c + 1
- For j = icol + 1 To UBound(arr, 2)
- If arr(i, j) <> "" Then re(icol + c, j) = arr(i, j)
- Next
- Else
- Exit For
- End If
- Next
- If Sheets.Count = 1 Then Sheets.Add , Sheets(1)
- Sheets(2).Cells.Clear
- Sheets(2).Range("A1").Resize(1, UBound(arr, 2)) = Sheet1.Rows(1).Resize(1, Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column).Value
- Sheets(2).Range("A2").Resize(icol + c, UBound(re, 2)) = re
- End Sub
复制代码 你没说会没有sheet2啊,哈哈 |
|