excel - 如何在excel中使用vba将不同的单元格值转换为单行？

Question

我想将数据从一张纸传输到另一张纸。

1. 我的 sheet1 是“表格”类型的工作表。我有命令按钮。
2. 按下命令按钮单元格值应复制到另一个 Sheet2。
3. 我希望值的每个副本都应该在一行和固定列中。
4. 它不应该被覆盖，而是进入下一个空行。

我正在使用以下 VBA 代码：

Private Sub Button1_Click()
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("A1,A2,B1,B2,B3")

    For Each cell In rng
        'here you copy to another sheet, one row lower
        Sheets("Sheet2").Cells(cell.Row + 1, cell.Column).Value = cell.Value
    Next cell

    For x = lRow To 2 Step -1
        If Range("I" & x) <> vbNullString Then Range("I" & x).EntireRow.Delete
    Next x

    Application.CutCopyMode = False
End Sub

标签： excelvba

Answer 1

Option Explicit

Sub test()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow As Long, i As Long
    Dim str As String, chara As Variant
    Dim rng As Range, cell As Range

    With ThisWorkbook
        Set ws1 = .Worksheets("Sheet1") '<- Set worksheet ws1
        Set ws2 = .Worksheets("Sheet2") '<- Set worksheet ws2
    End With

    Set rng = ws1.Range("B1:D1,B2:B6,B8:E11") '<- Set the range you want to loop

        For Each cell In rng

            If str = "" Then '<- Create a string with all details
            Else
                str = str & "," & cell.Value
            End If

        Next cell

        LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

        i = 1

        For Each chara In Split(str, ",") '<- Split with commas

            ws2.Cells(LastRow + 1, i) = chara

            i = i + 1

        Next chara

End Sub