|
帮忙一下,我想把A列%下T4一组坐标放在T1之前,T99一组坐标放在T01之后根据G2到I2设定参数,,H2前G2如有数据,I2后没有,就执行G2,总之G2后I2,有数据就执行,没数据就不执行。(一组是T* 上下相邻 ,如T1一组是与T1与T2之间 A列A9,A10。或T与M30 相邻为一组如T99一组是A22,A23(M30是结束意思)结果如B列,谢谢
本帖最后由 hwc2ycy 于 2012-12-19 20:27 编辑
- Option Explicit
- Sub 移位3()
- '2012-12-19 hwc2ycy 修改
- Dim str1$, str2$, str3$
- Dim pos1&, pos2&, pos3&
- Dim i&, i3&
-
- str1 = UCase(Range("g2"))
- str2 = UCase(Range("h2"))
- str3 = UCase(Range("i2"))
- On Error Resume Next
- ' 数据的局限性,必须以非数字开始,其后必须是数字
- If Len(str2) = 0 Then MsgBox "H2单元格无数据": Exit Sub
- If Len(str1) > 2 Then str1 = Left(str1, 1) & Right(str1, Len(str1) - 1) * 1
- If Len(str2) > 2 Then str2 = Left(str2, 1) & Right(str2, Len(str2) - 1) * 1
- If Len(str3) > 2 Then str3 = Left(str3, 1) & Right(str3, Len(str3) - 1) * 1
-
- If Len(str1) > 0 Then
- pos2 = Range("a:a").Find(str2, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "H2数据无法找到": Exit Sub
- pos1 = Range("a:a").Find(str1, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "G2数据无法找到": Exit Sub
- i = pos1 + 1
- 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
-
- Application.ScreenUpdating = False
- 'G2置前
- If pos2 <> i Then
- Range("a" & pos1 & ":a" & i - 1).Cut
- Range("a" & pos2).Insert xlShiftDown
- End If
- Application.ScreenUpdating = True
- End If
-
- If Len(str3) > 0 Then
- pos3 = Range("a:a").Find(str3, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "I2数据无法找到": Exit Sub
- '取后置行
- i = pos3 + 1
- Do Until Cells(i, 1) Like "T??" Or Cells(i, 1) Like "T?" Or Cells(i, 1) Like "M30" Or Cells(i, 1) Like "%"
- i = i + 1
- Loop
- i3 = i - 1
-
- '中间行
- pos2 = Range("a:a").Find(str2, , , xlWhole).Row
- If Err.Number <> 0 Then MsgBox "H2数据无法找到": Exit Sub
- i = pos2 + 1
- 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
-
- Application.ScreenUpdating = False
- If i <> pos3 Then
- Range("a" & pos3 & ":a" & i3).Cut
- Range("a" & i).Insert xlShiftDown
- End If
- Application.ScreenUpdating = True
- End If
- End Sub
复制代码
|
|