如何创建宏以拆分列和行？

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个长的工作表，其中包括3列：如何创建宏以拆分列和行？

柱A =发票号码
列B =接收量
列C - 支付金额

在列中的每个号码A需要分割到两行：付款和接收。
对于收费将采取准数从列C
对于接收将准数从塔B

回答：

我已经创建简单的VBA代码的行从工作表Sheet分成列向的Sheet 2中Activeworkbook。您可以根据需要修改代码。

Dim sel As Range, b As Range 
Dim rcount As Integer: rcount = 1 
ActiveWorkbook.Sheets(2).Range("A:C").Delete Shift:=xlDown 
With ActiveWorkbook.Sheets(1) 
Set sel = .Range("A2:C" & .UsedRange.Rows.Count) 
For Each b In sel.Rows 
ActiveWorkbook.Sheets(2).Cells(rcount, 1).Value = b.Value2(1, 1) 
ActiveWorkbook.Sheets(2).Cells(rcount, 2).Value = .Cells(1, 2).Value 
ActiveWorkbook.Sheets(2).Cells(rcount, 3).Value = b.Value2(1, 2) 
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 1).Value = b.Value2(1, 1) 
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 2).Value = .Cells(1, 3).Value 
ActiveWorkbook.Sheets(2).Cells(rcount + 1, 3).Value = b.Value2(1, 3) 
rcount = rcount + 2 
Next 
End With

回答：

假设你的数据在Sheet1，我们将让Sheet2的splited数据。这里有一个简单的解决方案：

Sub Split_Column() 
Set st1 = ThisWorkbook.Sheets("Sheet1") 
Set st2 = ThisWorkbook.Sheets("Sheet2") 
j = 1 
For i = 1 To st1.UsedRange.Rows.Count 
    st2.Cells(j, 1) = st1.Cells(i, 1) 
    st2.Cells(j, 2) = "Pay" 
    st2.Cells(j, 3) = st1.Cells(i, 3) 
    j = j + 1 
    st2.Cells(j, 1) = st1.Cells(i, 1) 
    st2.Cells(j, 2) = "Receive" 
    st2.Cells(j, 3) = st1.Cells(i, 2) 
    j = j + 1 
Next 
End Sub

以上是 如何创建宏以拆分列和行？ 的全部内容， 来源链接： utcz.com/qa/264484.html

回到顶部