|
请高手帮忙修改,谢谢!!
Sub Macro1()
Dim filearr As Variant, j As Integer
Dim rng As Range, i As Integer, sh As Worksheet
ChDrive Left(ThisWorkbook.Path, 1)
ChDir ThisWorkbook.Path
filearr = Application.GetOpenFilename(filefilter:="Excel文件,*.xls", MultiSelect:=True)
If IsArray(filearr) = False Then Exit Sub
For j = 1 To UBound(filearr)
With Workbooks.Open(filearr(j))
Set sh = .Sheets("Sheet2") '新加
For i = 1 To .Sheets.Count
With .Sheets(i)
If .Name <> "Sheet2" Then
For Each rng In .Range("j18:j23")
If rng.Value = 4 Then
sh.Range("d27").Value = sh.Range("d27").Value + rng.Offset(0, 1).Value
rng.Offset(0, 0) = 0
rng.Offset(0, 1) = 0
rng.Offset(0, 3) = 0
rng.Offset(0, 4) = 0
rng.Offset(0, 5) = 3
End If
Next
End If
End With
Next
.Close True
End With
Next
End Sub
本帖最后由 zjdh 于 2012-8-16 08:24 编辑
若要序号6的那一行消除则修改:
ARR(i, 1) = “”
ARR(i, 2) = “”
更甚者是将后续行上提: - Sub Macro1()
- Dim filearr As Variant, j As Integer
- Dim F As Integer, i As Integer, sh As Worksheet, ARR
- ChDir ThisWorkbook.Path
- filearr = Application.GetOpenFilename(filefilter:="Excel文件,*.xls", MultiSelect:=True)
- If IsArray(filearr) = False Then Exit Sub
- For F = 1 To UBound(filearr)
- With Workbooks.Open(filearr(F))
- Set sh = .Sheets("Sheet1")
- If Not sh Is Nothing Then
- ARR = sh.Range("j18:K23")
- For i = 1 To UBound(ARR)
- j=0
- If ARR(i, 1) = 6 Then
- For j = 1 To UBound(ARR)
- If ARR(j, 1) = 4 Then
- ARR(j, 2) = ARR(j, 2) + ARR(i, 2)
- For k = i To UBound(ARR) - 1
- ARR(k, 1) = ARR(k + 1, 1)
- ARR(k, 2) = ARR(k + 1, 2)
- Next
- ARR(k, 1) = ""
- ARR(k, 2) = ""
- Exit For
- End If
- Next
- End If
- If j > UBound(ARR) Then ARR(i, 1) = 4: Exit For
- Next
- sh.Range("j18:K23") = ARR
- .Close True
- End If
- End With
- Next
- End Sub
复制代码
|
|