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

Question

我在 excel(或 csv 文件)中有 2 个文本框，如下所示:
文本框 1 包含 (#11111,#22222,#33333)，文本框 2 包含 (#55555)

#11111,#22222,#33333  #55555

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

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

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

任何帮助，将不胜感激。

嗨@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

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

Answer 1

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

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

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

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

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

我希望这有帮助。

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