Các Sheet có mối liên kết với nhau
Option Explicit
Sub tonghophh()
Dim ngaybd, ngaykt As Long
ngaybd = Sheet3.Range("THHH_NGAYBD") * 1
ngaykt = Sheet3.Range("THHH_NGAYKT") * 1
Dim cn As Object, rst As Object
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
If Val(Application.Version) < 12 Then
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";"
Else
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
End If
.Open
End With
mySQL = "select c2,c3,c4,sum(c5),sum(c6),sum(c7),sum(c8),sum(c9),sum(c9)*(sum(c6)+sum(c8))/(sum(c5)+sum(c7)),sum(c11) as c10," & _
"sum(c5)+sum(c7)-sum(c9),sum(c6)+sum(c8)-sum(c9)*(sum(c6)+sum(c8))/(sum(c5)+sum(c7)) from(" & _
"select ma_hang as c2,ten_hang as c3,dvt as c4,so_luong as c5,thanh_tien as c6,0 as c7,0 as c8,0 as c9,0 as c11 from DMHH where ma_hang is not null " & _
"Union all " & _
"select ma_hang as c2,ten_hang as c3,dvt as c4,iif(ngay*1<" & (ngaybd) & ",so_luong,0) as c5" & _
",iif(ngay*1<" & (ngaybd) & ",thanh_tien,0) as c6,iif((ngay*1>=" & (ngaybd) & ")*(ngay*1<=" & (ngaykt) & "),so_luong,0) as c7," & _
"iif((ngay*1>=" & (ngaybd) & ")*(ngay*1<=" & (ngaykt) & "),thanh_tien,0) as c8,0 as c9,0 as c11 from NK_NHAP where ma_hang is not null " & _
"Union all " & _
"select ma_hang as c2,ten_hang as c3,dvt as c4,iif(ngay*1<" & (ngaybd) & ",-so_luong,0) as c5" & _
",iif(ngay*1<" & (ngaybd) & ",-thanh_tien,0) as c6,0 as c7,0 as c8,iif((ngay*1>=" & (ngaybd) & ")*(ngay*1<=" & (ngaykt) & "),so_luong,0) as c9," & _
"iif((ngay*1>=" & (ngaybd) & ")*(ngay*1<=" & (ngaykt) & "),thanh_tien,0) as c11 from NK_XUAT where ma_hang is not null) " & _
"Group by c2,c3,c4"
Set rst = cn.Execute(mySQL)
With Sheet3
.[A22:M100000].Clear
.[B22].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing
End Sub
Sub tinhtong_va_Stt()
Dim t, i As Long
t = Sheet3.Range("B200000").End(xlUp).Row
If t > 21 Then
Sheet3.Range("E20:M20").FormulaR1C1 = "=SUM(R22C:R" & (t) & "C)"
Sheet3.Range("A22:A" & t).FormulaR1C1 = "=R[-1]C+1"
For i = 0 To 8
Sheet3.Range("E22").Offset(, i).Resize(t - 21, 1).NumberFormat = Sheet3.Range("E21").Offset(, i).NumberFormat
Next
End If
End Sub
Sub kevien()
Dim t As Integer, rng As Range
t = Sheet3.Range("B200000").End(xlUp).Row
Set rng = Sheet3.Range("A22:M" & t)
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
