根据列数据创建新的Excel行 [英] Create new Excel rows based on column data

问题描述

大家下午好，

我遇到的问题是，我的用户拥有多个银行帐户详细信息.我需要尝试为每个拥有多个银行帐户的员工创建一个新行，并为第二个银行帐户分配一个新行.

I have an issue where I have users who have multiple bank account details. I need to try and create a new row for each employee who has more than one bank account, with the second bank account being allocated a new row.

Employee Number     User ID         BSB             Account number
10000591            WOODSP0         306089,116879   343509,041145273
10000592            THOMSOS0        037125          317166

我需要它看起来像这样:

I need it to look something like this:

Employee Number     User ID     BSB     Account number
10000591            WOODSP0     306089  343509
10000591            WOODSP0     116879  041145273
10000592            THOMSOS0    037125  317166

有什么想法吗?非常感谢您的输入！

Any thoughts? Your input is greatly appreciated!

此处显示的屏幕截图是

推荐答案

这是另一个子程序，似乎可以执行您想要的操作.

Here is another sub that appears to perform what you are looking for.

Sub stack_accounts()
    Dim rw As Long, b As Long
    Dim vVALs As Variant, vBSBs As Variant, vACTs As Variant

    With ActiveSheet   '<-define this worksheet properly!
        For rw = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            vVALs = .Cells(rw, 1).Resize(1, 4).Value
            vBSBs = Split(vVALs(1, 3), Chr(44))
            vACTs = Split(vVALs(1, 4), Chr(44))
            If UBound(vBSBs) = UBound(vBSBs) Then
                For b = UBound(vBSBs) To LBound(vBSBs) Step -1
                    If b > LBound(vBSBs) Then _
                        .Rows(rw + 1).Insert
                    .Cells(rw - (b > LBound(vBSBs)), 1).Resize(1, 4) = vVALs
                    .Cells(rw - (b > LBound(vBSBs)), 3).Resize(1, 2).NumberFormat = "@"
                    .Cells(rw - (b > LBound(vBSBs)), 3) = CStr(vBSBs(b))
                    .Cells(rw - (b > LBound(vBSBs)), 4) = CStr(vACTs(b))
                Next b
            End If
        Next rw
    End With
End Sub

我原本只打算处理C和D列中以逗号分隔的值的行，但是我认为处理所有这些行将允许宏设置 Text 数字格式并摆脱它. Number as text 错误警告，并在 041145273 中保留前导零.

I was originally only going to process the rows that had comma delimited values in columns C and D but I thought that processing all of them would allow the macro to set the Text number format and get rid of the Number as text error warnings and keep the leading zero in 041145273.

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