在 VBA 中将字符串文本拆分为单独的行

splitting a string text into separate rows in VBA

提问by Tony Dallimore

I have 2 text boxes in a excel (or csv file) as below: text box 1 contains (#11111,#22222,#33333), text box 2 contains (#55555)

我在 excel（或 csv 文件）中有 2 个文本框，如下所示：文本框 1 包含（#11111、#22222、#33333），文本框 2 包含（#55555）

#11111,#22222,#33333  #55555

I want the text between , to be on 3 different rows and repeat the text in 2nd text box so that it looks like below:

我希望 , 之间的文本位于 3 个不同的行上，并在第二个文本框中重复文本，使其如下所示：

#11111   #55555
#22222   #55555
#33333   #55555

I am new to VBA. I am reading about string functions but I can't come up with logic on how to do it.

我是 VBA 的新手。我正在阅读有关字符串函数的内容，但我无法想出有关如何执行此操作的逻辑。

Any help would be appreciated.

任何帮助，将不胜感激。

Hi @tim williams - Thanks for the advice. I did manage to write a short code which accomplishes the task but it overwrites the text if I have any in 2nd row and 3rd row.

嗨@tim williams - 感谢您的建议。我确实设法编写了一个短代码来完成任务，但是如果我在第二行和第三行中有任何文本，它会覆盖文本。

 Sub splitcells()
 Dim txt As String
 Dim txt2 As String

 Dim i As Integer
 Dim cell1 As Variant

 txt = Range("a1", "A1").Value
 cell1 = Split(txt, ",")
 For i = 0 To UBound(cell1)
 Cells(i + 1, 1).Value = cell1(i)
 Next i

 txt2 = Range("b1", "b1")
 For i = 1 To UBound(cell1)
 Cells(i + 1, 2).Value = txt2
 Next i

 End Sub

Any advice on how to push the data on row 2 downwards .....

关于如何将第 2 行的数据向下推送的任何建议.....

回答by Tony Dallimore

I do not know how to give you a hint that would help you adjust your macro so I have coded what I think you are after.

我不知道如何给你一个提示来帮助你调整你的宏，所以我已经编码了我认为你想要的东西。

You talk about overwriting data in the 2nd or 3rd row so I assume you have several rows containing data in this format. I have therefore converted your code into a loop that works down column A until it finds a blank row.

您谈论覆盖第 2 行或第 3 行中的数据，因此我假设您有几行包含这种格式的数据。因此，我已将您的代码转换为一个循环，该循环在 A 列下工作，直到找到一个空白行。

I avoid overwriting data below the current row by inserting rows as necessary.

我通过根据需要插入行来避免覆盖当前行下方的数据。

I have changed your code in ways that I believe makes the code more maintainable. I have explained my reasons for these changes.

我以我认为使代码更易于维护的方式更改了您的代码。我已经解释了这些变化的原因。

I have not explained the new statements I have used. It is generally easy to look up a statement once you know it exists but do ask questions if anything is unclear.

我没有解释我使用的新语句。一旦您知道它存在，通常很容易查找声明，但如果有任何不清楚的地方，请提出问题。

I hope this helps.

我希望这有帮助。

Option Explicit
Sub splitcells()

  ' * With VBA, Integer declares a 16-bit value while Long declares a 32-bit
  '   value. 16-bit values require special processing and are slower. So
  '   Long is preferred.
  ' * I do not like variable names such as i.  It does not really matter with
  '   a tiny macro but with a larger macro it does.  It does not matter now
  '   but it matters when you return to this macro in 6 or 12 months to amend
  '   it.  You want to be able to look at variables and immediately know what
  '   they are.  I have named variables according to my system.  I am not
  '   asking you to like my system but to have a system.  I can return to
  '   macros I wrote years ago and immediately recognise all the variables.
  Dim InxSplit As Long
  ' Dim i As Integer

  ' * Split returns a string array.  A Variant can be hold a string array but
  '   access is slower.  Variants can be very useful but only use then when
  '   you need the flexibility they offer.
  Dim SplitCell() As String
  ' Dim cell1 As Variant

  Dim RowCrnt As Long

  ' * "Range" operates on the active worksheet.  You are relying on the correct
  '   worksheet being active when the macro is called.  Also, when you return
  '   to the macro in 6 or 12 months will you remember which worksheet is
  '   supposed to be active.  ".Range" operates on the worksheet specified in
  '   the With statement.  It doe not matter which worksheet is active and it
  '   is absolutely clear which worksheet is the target of this code.
  With Worksheets("Sheet1")

    RowCrnt = 1         ' The first row containing data.

    Do While True

      ' * I use .Cells(row, column) rather than .Range because it is more
      '   convenient when you need to change the row and/or column numbers.
      ' * Note the column value can be a number or a column identifier.
      '   A = 1, B=2, Z=26, AA = 27, etc.  I am not doing arithmetic with
      '   the columns so I have used "A" and "B" which I find more
      '   meaningful than 1 and 2.
      If .Cells(RowCrnt, "A").Value = "" Then
        Exit Do
      End If

      SplitCell = Split(.Cells(RowCrnt, "A").Value, ",")

      If UBound(SplitCell) > 0 Then
        ' The cell contained a comma so this row is to be spread across
        ' two or more rows.
        ' Update the current row
        .Cells(RowCrnt, "A").Value = SplitCell(0)

        ' For each subsequent element of the split value, insert a row
        ' and place the appropriate values within it.
        For InxSplit = 1 To UBound(SplitCell)
          RowCrnt = RowCrnt + 1
          ' Push the rest of the worksheet down
          .Rows(RowCrnt).EntireRow.Insert
          ' Select the appropriate part of the original cell for this row
          .Cells(RowCrnt, "A").Value = SplitCell(InxSplit)
          ' Copy the value from column B from the previous row
          .Cells(RowCrnt, "B").Value = .Cells(RowCrnt - 1, "B").Value
        Next
      End If

      RowCrnt = RowCrnt + 1

    Loop

  End With

 End Sub