文本行VBA Excel [英] Text to Rows VBA Excel

问题描述

我有一个约4000行数据的电子表格，其中一列数据具有独特的订单号，我想使用/作为分隔符分隔。所以本质上我想要：

I have a spreadsheet with about a good 4000 rows of data, one of the columns of data has unique order numbers that I want separated using "/" as my delimiter. So essentially I want:

Name      Order#       Date
Jane      123/001/111  08/15/2013
Gary      333/121      09/01/2013
Jack      222          09/02/2013

看起来像这样：

Name      Order#       Date
Jane      123          08/15/2013
Jane      001          08/15/2013
Jane      111          08/15/2013
Gary      333          09/01/2013
Gary      121          09/01/2013
Jack      222          09/02/2013

我对VBA来说相当新鲜，所以我决定尝试Google来解决我遇到的问题这个很好的代码。

I am fairly new to VBA, so I decided to try to Google for a solution where I came upon this nice bit of code.

        Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim ans 
    Dim Cels As Long, i As Long 
    Cancel = True 
    ans = Split(Target, ":") 
    Cels = UBound(ans) 
    Target.Offset(1).Resize(Cels).EntireRow.Insert shift:=xlDown 
    Rows(Target.Row).Copy Cells(Target.Row + 1, "A").Resize(Cels) 
    For i = 0 To Cels 
        Target.Offset(i) = ans(i) 
    Next 
End Sub

伟大的，但这个宏的功能是你必须双击行来分离值。我希望的是通过一个For循环传递这个函数的方法，所以它在整个电子表格中执行。

It works great but the way this macro functions is that you have to double click the row to separate the values. What I was hoping for is a way to pass this function through a For loop so it executes throughout the entire spreadsheet.

推荐答案

如果你的表格看起来像这样

If you sheet looks sort of like this

然后

Option Explicit

Sub Main()

    Columns("B:B").NumberFormat = "@"
    Dim i As Long, c As Long, r As Range, v As Variant

    For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
        v = Split(Range("B" & i), "/")
        c = c + UBound(v) + 1
    Next i

    For i = 2 To c
        Set r = Range("B" & i)
        Dim arr As Variant
        arr = Split(r, "/")
        Dim j As Long
        r = arr(0)
        For j = 1 To UBound(arr)
            Rows(r.Row + j & ":" & r.Row + j).Insert Shift:=xlDown
            r.Offset(j, 0) = arr(j)
            r.Offset(j, -1) = r.Offset(0, -1)
            r.Offset(j, 1) = r.Offset(0, 1)
        Next j
    Next i

End Sub

将产生

这篇关于文本行VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！