سلام
جواب سوال خودم (شاید برای دوستان مفید باشه )
کد PHP:
Private Sub CommandButton1_Click()
Workbooks.Open Filename:="D:\report.XLSX"
Windows("report.XLSX").Visible = False
Dim a, b As Range
For Each b In Workbooks("report.XLSX").Worksheets("report").Range("L1:L10000")
For Each a In Sheet1.Range("A1:A1000")
If Application.WorksheetFunction.CountIfs(Sheet1.Range("A1:A1000"), b.Value, _
Sheet1.Range("B1:B1000"), b.Offset(0, -6), Sheet1.Range("D1:D1000"), _
b.Offset(0, -3)) = 0 And a.Value = "" Then
a.Value = b.Value
a.Offset(0, 2).Value = b.Offset(0, -4).Value
a.Offset(0, 1).Value = b.Offset(0, -6).Value
a.Offset(0, 3).Value = b.Offset(0, -3).Value
a.Offset(0, 4).Value = Application.WorksheetFunction.SumIfs(Workbooks("report.XLSX").Worksheets("report").Range("B:B"), _
Workbooks("report.XLSX").Worksheets("report").Range("l:l"), a.Value, _
Workbooks("report.XLSX").Worksheets("report").Range("F:F"), a.Offset(0, 1), _
Workbooks("report.XLSX").Worksheets("report").Range("I:I"), a.Offset(0, 3), _
Workbooks("report.XLSX").Worksheets("report").Range("C:C"), "<=" & Sheet1.Range("i1")) - _
Application.WorksheetFunction.SumIfs(Workbooks("report.XLSX").Worksheets("report").Range("A:A"), _
Workbooks("report.XLSX").Worksheets("report").Range("l:l"), a.Value, _
Workbooks("report.XLSX").Worksheets("report").Range("F:F"), a.Offset(0, 1), _
Workbooks("report.XLSX").Worksheets("report").Range("I:I"), a.Offset(0, 3), _
Workbooks("report.XLSX").Worksheets("report").Range("C:C"), "<=" & Sheet1.Range("i1"))
Sheet1.Range("E1") = "ÌãÚ ÈÏåí"
If a.Offset(0, 4).Value = 0 Then Rows(a.Row).Delete Shift:=xlUp
Exit For
End If
Next
Next
Windows("report.XLSX").Visible = True
Workbooks("report.XLSX").Close SaveChanges:=False
End Sub
علاقه مندی ها (Bookmarks)