HOANGHUUTUAN
Thành viên mới
- Tham gia
- 29/8/08
- Bài viết
- 26
- Được thích
- 0
Sub abc()
Dim i As Long
Dim lr As Long
Dim lr1 As Long
Dim vaData As Variant
Dim colUnique As Collection
Dim aOutput() As Variant
Dim j As Long
Dim aa As Range
Dim bb As Range
Dim cc As Range
Dim dd As Range
Dim ee As Range
Dim Ngaydau As Date, Ngaycuoi As Date
lr1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
vaData = Worksheets("data").Range("A1:G" & lr1).Value
Set colUnique = New Collection
Set aa = Sheet1.Range("A1:A" & lr1)
Set bb = Sheet1.Range("B1:B" & lr1)
Set cc = Sheet1.Range("H1:H" & lr1)
Set dd = Sheet1.Range("I1:I" & lr1)
Set ee = Sheet1.Range("J1:J" & lr1)
For i = LBound(vaData, 1) To UBound(vaData, 1)
On Error Resume Next
colUnique.Add vaData(i, 1), CStr(vaData(i, 1))
On Error GoTo 0
Next i
ReDim aOutput(1 To colUnique.Count, 1 To 1)
For i = 1 To colUnique.Count
aOutput(i, 1) = colUnique.Item(i)
Next i
Sheet2.Range("A2").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
Ngaydau = CDate(Sheet2.Range("I10").Value)
Ngaycuoi = CDate(Sheet2.Range("J10").Value)
For j = 1 To lr - 1
'Cells(j + 1, 2).Value = Application.WorksheetFunction.SumIf(Sheets("data").Range("A1:A" & lr1), Sheet2.Cells(j + 1, 1), Sheets("data").Range("B1:B" & lr1))
Cells(j + 1, 2).Value = Application.WorksheetFunction.SumIf(aa, Sheet2.Cells(j + 1, 1), bb)
'Cells(j + 1, 4).Value = Application.WorksheetFunction.SumIf(Sheets("data").Range("A1:A" & lr1), Sheet2.Cells(j + 1, 1), Sheets("data").Range("H1:H" & lr1))
Cells(j + 1, 4).Value = Application.WorksheetFunction.SumIf(aa, Sheet2.Cells(j + 1, 1), cc)
Cells(j + 1, 6).Value = Application.WorksheetFunction.SumIfs(dd, aa, Sheet2.Cells(j + 1, 1), ee, ">=" & Ngaydau, ee, "<=" & Ngaycuoi)
Next j
End Sub
Dim i As Long
Dim lr As Long
Dim lr1 As Long
Dim vaData As Variant
Dim colUnique As Collection
Dim aOutput() As Variant
Dim j As Long
Dim aa As Range
Dim bb As Range
Dim cc As Range
Dim dd As Range
Dim ee As Range
Dim Ngaydau As Date, Ngaycuoi As Date
lr1 = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
vaData = Worksheets("data").Range("A1:G" & lr1).Value
Set colUnique = New Collection
Set aa = Sheet1.Range("A1:A" & lr1)
Set bb = Sheet1.Range("B1:B" & lr1)
Set cc = Sheet1.Range("H1:H" & lr1)
Set dd = Sheet1.Range("I1:I" & lr1)
Set ee = Sheet1.Range("J1:J" & lr1)
For i = LBound(vaData, 1) To UBound(vaData, 1)
On Error Resume Next
colUnique.Add vaData(i, 1), CStr(vaData(i, 1))
On Error GoTo 0
Next i
ReDim aOutput(1 To colUnique.Count, 1 To 1)
For i = 1 To colUnique.Count
aOutput(i, 1) = colUnique.Item(i)
Next i
Sheet2.Range("A2").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
Ngaydau = CDate(Sheet2.Range("I10").Value)
Ngaycuoi = CDate(Sheet2.Range("J10").Value)
For j = 1 To lr - 1
'Cells(j + 1, 2).Value = Application.WorksheetFunction.SumIf(Sheets("data").Range("A1:A" & lr1), Sheet2.Cells(j + 1, 1), Sheets("data").Range("B1:B" & lr1))
Cells(j + 1, 2).Value = Application.WorksheetFunction.SumIf(aa, Sheet2.Cells(j + 1, 1), bb)
'Cells(j + 1, 4).Value = Application.WorksheetFunction.SumIf(Sheets("data").Range("A1:A" & lr1), Sheet2.Cells(j + 1, 1), Sheets("data").Range("H1:H" & lr1))
Cells(j + 1, 4).Value = Application.WorksheetFunction.SumIf(aa, Sheet2.Cells(j + 1, 1), cc)
Cells(j + 1, 6).Value = Application.WorksheetFunction.SumIfs(dd, aa, Sheet2.Cells(j + 1, 1), ee, ">=" & Ngaydau, ee, "<=" & Ngaycuoi)
Next j
End Sub