本帖最后由 fangniuji 于 2012-12-19 12:43 编辑
帮忙一下,H2是作用对象,G2以下是如G1描述(放序号下面数据)I2以下如G2以下描述(放H2序号后面数据)H2前面G2或后面I2,G2和I2同时有数据就同时执行,数据源A列结果如B列,如果I2以下没数据时,结果如F列,如果G2以下没数据,结果如J列.G 2和I2 以下不一定有几个数据,谢谢。
- Option Explicit
- Sub 放资料()
- '2012-12-19 hwc2ycy 修改
- Dim str2$, pos2&
- Dim rg1 As Range '序号下面的数据
- Dim rg3 As Range, pos3& '放在序号后面的数据
-
- Dim i&, i3&
-
- str2 = UCase(Range("h2"))
-
- On Error Resume Next
- ' 数据的局限性,必须以非数字开始,其后必须是数字
- If Len(str2) = 0 Then MsgBox "H2单元格无数据": Exit Sub
- If Len(str2) > 2 Then str2 = Left(str2, 1) & Right(str2, Len(str2) - 1) * 1
-
- i = Cells(Rows.Count, "g").End(xlUp).Row
- If i >= 2 Then Set rg1 = Range("g2:g" & i)
- i = Cells(Rows.Count, "i").End(xlUp).Row
- If i >= 2 Then Set rg3 = Range("i2:i" & i)
-
- '放序号下面的数据
- If Not rg1 Is Nothing Then
- pos2 = Range("a:a").Find(str2, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "H2数据无法找到": Exit Sub
- Application.ScreenUpdating = False
- rg1.Copy
- Range("a" & pos2 + 1).Insert xlShiftDown
- Application.CutCopyMode = False
- pos3 = pos2 + 1 + rg1.Count
- Application.ScreenUpdating = True
- End If
-
- '放序号后面数据
- If Not rg3 Is Nothing Then
- If pos3 = 0 Then
- pos2 = Range("a:a").Find(str2, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "H2数据无法找到": Exit Sub
- i = pos2 + 1
- Else
- i = pos3
- End If
- Do Until Cells(i, 1) Like "T??" Or Cells(i, 1) Like "T?" Or Len(Cells(i, 1)) = 0 Or Cells(i, 1) Like "%" Or Cells(i, 1) Like "M30"
- i = i + 1
- Loop
-
- pos3 = i
- Application.ScreenUpdating = False
- rg3.Copy
- Range("a" & pos3).Insert xlShiftDown
- Application.CutCopyMode = False
- Application.ScreenUpdating = True
- End If
- End Sub
复制代码
|