|
Sub 按钮3_Click()
Dim x As Integer
Dim y As Integer
Dim i As Long
Dim j As Long
y = 6
While Sheet1.Cells(y, 1) <> ""
x = 6
While Sheet2.Cells(x, 1) <> ""
If Sheet2.Cells(x, 5) = Sheet1.Cells(y, 4) Then
j = Sheet3.Cells.Rows.Count
i = Sheet3.Range("a" & j).End(xlUp).Row + 1
Sheet3.rang("a" & i) = Sheet1.Cells(y, 3)
Sheet3.rang("b" & i) = Sheet1.Cells(y, 4)
Sheet3.rang("c" & i) = Sheet1.Cells(y, 5)
Sheet3.rang("d" & i) = Sheet1.Cells(y, 6)
Sheet3.rang("e" & i) = Sheet1.Cells(y, 7)
Sheet3.rang("f" & i) = Sheet2.Cells(x, 5)
Sheet3.rang("g" & i) = Sheet1.Cells(y, 10)
End If
x = x + 1
Wend
y = y + 1
Wend
End Sub
这段程序运行不了,不知是什么原因?
你的sheet1和sheet2中没有配号编码相同的,代码如下: - Sub 配号()
- Dim d, arr(), i%, n%
- Set d = CreateObject("Scripting.Dictionary")
- With Sheet1
- n = .Cells(.Rows.Count, 4).End(xlUp).Row
- For i = 6 To n
- d.Add .Cells(i, 4).Value, i - 5
- ReDim Preserve arr(1 To 7, 1 To i - 5)
- arr(1, i - 5) = .Cells(i, 3)
- arr(2, i - 5) = .Cells(i, 4)
- arr(3, i - 5) = .Cells(i, 5)
- arr(4, i - 5) = .Cells(i, 6)
- arr(5, i - 5) = .Cells(i, 7)
- arr(6, i - 5) = .Cells(i, 10)
- Next
- End With
- With Sheet2
- n = .Cells(.Rows.Count, 2).End(xlUp).Row
- For i = 6 To n
- If d.Exists(.Cells(i, 2).Value) Then arr(7, d(.Cells(i, 2).Value)) = .Cells(i, 5)
- Next
- End With
- x = 1
- For Each k In d.keys
- If arr(7, d(k)) <> "" Then
- x = x + 1
- Sheet3.Cells(x, 1).Resize(1, 7) = Application.WorksheetFunction.Transpose(Application.Index(arr, 0, d(k)))
- End If
- Next
- End Sub
复制代码
|
|