2,462
社区成员
发帖
与我相关
我的任务
分享
Option Explicit
Private Sub TEST()
Dim dataSource() As String '存放原始数据
Dim dataResult() As String '存放结果数据
Dim rowSource As Long '原始记录行号
Dim rowResult As Long '结果记录行号
Dim rowCopyL As Long '要复制的行号(长整行)
Dim rowCopyS As String '要复制的行号(字符串)
Dim posPlus As Integer '加号的位置
Dim i As Integer
Dim j As Integer
rowResult = 0
rowSource = 1 ' 从第1行开始,无标题行
While Cells(rowSource, 2) <> "" '先把原来的数据存起来
ReDim Preserve dataSource(5, rowSource)
For i = 1 To 5
dataSource(i, rowSource) = Cells(rowSource, i)
Next
rowResult = rowResult + 1
ReDim Preserve dataResult(5, rowResult)
posPlus = InStr(1, dataSource(2, rowSource), "+") '取得“+”的位置
If posPlus <> 0 And Trim(dataSource(2, rowSource)) <> "+" Then '本行第二列中含有“+”,且不是“+”
rowCopyS = Mid(dataSource(2, rowSource), posPlus + 1) '取得“+”后面的数字
If IsNumeric(rowCopyS) Then '“+”后面有数字的情况,复制对应行
rowCopyL = rowSource - 1 - CInt(rowCopyS)
Else '“+”后没有数字的情况,复制上一行
rowCopyL = rowSource - 1
End If
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowCopyL)
Next
dataResult(2, rowResult) = Mid(dataSource(2, rowSource), 1, posPlus - 1) '处理第2列数据为“+”前面的内容
rowResult = rowResult + 1
ReDim Preserve dataResult(5, rowResult)
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowSource)
Next
dataResult(2, rowResult) = "+" '处理第2列数据为“+”
Else '本行第二列中不含有“+”,或者第二列为“+”,原样输出
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowSource)
Next
End If
rowSource = rowSource + 1
Wend
For i = 1 To rowResult
For j = 1 To 5
Cells(i, j + 6) = dataResult(j, i)
Next
Next
End Sub
麻烦再细化一下你的问题,没看懂你到底要干什么。
Option Explicit
Private Sub TEST()
Dim dataSource() As String '存放原始数据
Dim dataResult() As String '存放结果数据
Dim rowSource As Long '原始记录行号
Dim rowResult As Long '结果记录行号
Dim rowCopyL As Long '要复制的行号(长整行)
Dim rowCopyS As String '要复制的行号(字符串)
Dim posPlus As Integer '加号的位置
Dim i As Integer
Dim j As Integer
rowResult = 0
rowSource = 2 ' 从第2行开始,第1行为标题
While Cells(rowSource, 2) <> "" '先把原来的数据存起来
ReDim Preserve dataSource(5, rowSource - 1)
For i = 1 To 5
dataSource(i, rowSource - 1) = Cells(rowSource, i)
Next
rowResult = rowResult + 1
ReDim Preserve dataResult(5, rowResult)
posPlus = InStr(1, dataSource(2, rowSource - 1), "+") '取得“+”的位置
If posPlus <> 0 Then '本行第二列中含有“+”
rowCopyS = Mid(dataSource(2, rowSource - 1), posPlus + 1) '取得“+”后面的数字
If IsNumeric(rowCopyS) Then '“+”后面有数字的情况,复制对应行
rowCopyL = rowSource - 2 - CInt(rowCopyS)
Else '“+”后没有数字的情况,复制上一行
rowCopyL = rowSource - 2
End If
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowCopyL)
Next
dataResult(2, rowResult) = Mid(dataSource(2, rowSource - 1), 1, posPlus - 1) '处理第2列数据为“+”前面的内容
rowResult = rowResult + 1
ReDim Preserve dataResult(5, rowResult)
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowSource - 1)
Next
dataResult(2, rowResult) = "+" '处理第2列数据为“+”
Else '本行第二列中不含有“+”,原样输出
For i = 1 To 5
dataResult(i, rowResult) = dataSource(i, rowSource - 1)
Next
End If
rowSource = rowSource + 1
Wend
For i = 2 To rowResult + 1
For j = 1 To 5
Cells(i, j + 6) = dataResult(j, i - 1)
Next
Next
End Sub