VBA-拆分单元格值并将其放入下面的行 [英] VBA - Split cell values and put it into the row below
本文介绍了VBA-拆分单元格值并将其放入下面的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找将:"之后的最后一行的单元格拆分的代码,如下所示:
I am looking for a code that would split the cells of the last row after ":" as shown below:
之前
之后
到目前为止,拆分数据看起来像这样-但仅适用于第一个单元格:
So far splitting data looks like this - but it only works for the first cell:
Sub test()
Dim ws As Worksheet
Set ws = Sheets("Sheet3")
Dim fullstring As String, colonposition As Integer, j As Integer, LastRow As Long, FirstRow As Long
Dim lRow As Long, lCol As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To 3
fullstring = Cells(lRow, lCol).Value
colonposition = InStr(fullstring, ":")
Cells(lRow, j).Value = Left(fullstring, colonposition - 1)
lRow = lRow + 1
Cells(lRow, j).Value = Mid(fullstring, colonposition + 1)
Next
End Sub
I have found a similar problematic (with answer) here but can't manage to apply it ONLY to the last row
任何建议表示赞赏!
推荐答案
如果有人对@Ondrej讨论的答案感兴趣,这里有两个代码,第一个是静态的,第二个是动态的:>
In case some people are interested in the answers to the discussion with @Ondrej, here are two codes, the first is static and the second is dynamic:
Sub test()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Dim actualRange As Range
For Each actualRange In ws.Range(ws.Cells(ws.Rows.Count, 1).End(xlUp), ws.Cells(ws.Rows.Count, 1).End(xlUp).End(xlToRight))
If InStr(Trim(actualRange), ":") > 0 Then
actualRange.Offset(1, 0).Value = Split(actualRange.Value, ":")(1)
actualRange.Offset(0, 0).Value = Split(actualRange.Value, ":")(0)
End If
Next
End Sub
&
Sub test()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Dim actualRange As Range
Dim tmpString As String
For Each actualRange In ws.Range(ws.Cells(ws.Rows.Count, 1).End(xlUp), ws.Cells(ws.Rows.Count, 1).End(xlUp).End(xlToRight))
tmpString = actualRange.Value
If InStr(Trim(tmpString), ":") > 0 Then
actualRange.Offset(0, 0).Value = Split(tmpString, ":")(0)
actualRange.Offset(1, 0).Value = Split(tmpString, ":")(1)
End If
Next
End Sub
这篇关于VBA-拆分单元格值并将其放入下面的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文