条件:A1:D5为源数据,A10:H14为目标数据 目标:用VBA代码实现字符串拆分(多行单列的字符串已可以实现,现求助多行多列字符串如何拆分)?感谢! 因数据的涉密性,故做了示意数据,请见谅。
根据你这附件做的,和你那个模拟的一样 了,实际的你自己参考代码做修改吧
- Option Explicit
- Sub test()
- Dim arrSrc, arrRst(), arrSplt
- Dim irow%, icol%, iCnt%, Cnt%
- arrSrc = Range("a1").CurrentRegion.Value
- ReDim arrRst(1 To UBound(arrSrc), 1 To UBound(arrSrc, 2) * 2)
- For irow = 2 To UBound(arrSrc)
- For icol = 1 To UBound(arrSrc, 2)
- If Len(arrSrc(irow, icol)) > 0 Then
- arrSplt = Split(arrSrc(irow, icol), Chr(10))
- Else
- arrSplt = Split(arrSrc(irow, icol - 1), Chr(10))
- End If
- For iCnt = 0 To UBound(arrSplt)
- Cnt = Cnt + 1
- arrRst(irow, Cnt) = arrSplt(iCnt)
- Next
- Erase arrSplt
- Next
- Cnt = 0
- Next
- arrRst(1, 1) = arrSrc(1, 1)
- Range("a10").Resize(1, UBound(arrRst, 2)).Merge
- With Range("a10").Resize(UBound(arrRst), UBound(arrRst, 2))
- .Value = arrRst
- .HorizontalAlignment = xlCenter
- .HorizontalAlignment = xlCenter
- .Borders.LineStyle = True
- .Font.Size = 10
- End With
- End Sub
复制代码
|