我收到一個格式如下的 excel 檔案：
但我需要它是以下格式：
我有打擊代碼，但它不起作用。
Sub Format_Click()
Dim ws1 As Worksheet
Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
Dim count As Integer
Dim rng As Range
Set rng = ws1.UsedRange
ws2.Cells(1, 1) = "Contract"
ws2.Cells(1, 2) = "Code"
ws2.Cells(1, 3) = "Price"
For i = 1 To rng.Columns.count
For j = 2 To rng.Rows.count
count = ws2.Range("A" & ws2.Rows.count).End(xlUp).Row
ws2.Cells(count 1, 1) = rng.Cells(1, i)
ws2.Cells(count 1, 2) = rng.Cells(j, i)
ws2.Cells(count 1, 3) = rng.Cells(j, 1)
Next j
Next i
End Sub
uj5u.com熱心網友回復：
將所有資料放入一個陣列中，回圈一次并檢測類別（類別是根據您的影像沒有數量的值）。
我的代碼在同一張表中顯示輸出，但可以很容易地調整以在不同的作業表中進行輸出：
Sub test()
Dim i As Long
Dim j As Long
Dim LR As Long
Dim MyData As Variant
Dim CurrentCat As String
LR = Range("A" & Rows.Count).End(xlUp).Row
MyData = Range("A1:B" & LR).Value
Range("D1").Value = "Category"
Range("E1").Value = "Name"
Range("F1").Value = "Qty"
j = 2
For i = LBound(MyData) To UBound(MyData) Step 1
If MyData(i, 2) = "" Then
'its a Category if there is no qty
CurrentCat = MyData(i, 1)
Else
'there is data
Range("D" & j).Value = CurrentCat
Range("E" & j).Value = MyData(i, 1)
Range("F" & j).Value = MyData(i, 2)
j = j 1
End If
Next i
Erase MyData
End Sub
考慮閱讀陣列，非常有用：
Excel VBA 陣列 – 完整指南
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/521992.html
標籤：擅长vba
上一篇：如何使用vba洗掉重復的作業表？
下一篇：回圈并列出問題