quyenpv
Thu nhặt kiến thức
- Tham gia
- 5/1/13
- Bài viết
- 725
- Được thích
- 97
- Giới tính
- Nam
- Nghề nghiệp
- Decode cuộc đời!
Em đang làm file dự toán, vướng mắc mới tổng hợp được vật liệu từ bảng PTVT. Do nhu cầu cần tổng hợp ật liệu, nhân công, máy thi công tư bảng PTVT mà trình độ không đủ
Mong anh chị giúp đỡ sửa code để hoàn thiện file là việc, cám ơn anh chị
Mong anh chị giúp đỡ sửa code để hoàn thiện file là việc, cám ơn anh chị
Mã:
Sub TongVatTu()
Application.ScreenUpdating = False
Sheets("THVT").Select
'Loc lay vat tu
Dim N As Long, m As Long
m = Sheets("THVT").Range("C65000").End(xlUp).Row
If m > 9 Then
Sheets("THVT").Select
Rows("10:" & m + 1).Select
Selection.Delete Shift:=xlUp
End If
'Gan n la dong cuoi cung co du lieu cua cot I (Cot Tong KL) Sheets("PTVT")
N = Sheets("PTVT").Range("I65000").End(xlUp).Row
For I = 7 To N
If Sheets("PTVT").Cells(I, 5) <> "công" And Sheets("PTVT").Cells(I, 5) <> "ca" And Sheets("PTVT").Cells(I, 5) <> "%" Then
m = Sheets("THVT").Range("C65000").End(xlUp).Row
DVT = Sheets("PTVT").Cells(I, 5)
If Sheets("PTVT").Cells(I, 7) <> 0 Then
Sheets("THVT").Cells(m + 1, 2) = Sheets("PTVT").Cells(I, 4)
Sheets("THVT").Cells(m + 1, 3) = DVT
Sheets("THVT").Cells(m + 1, 9) = "=COUNTIF(R10C2:RC2,RC[-7])" ' Cot I cua Sheet THVT
End If
End If
Next
'Xoa Vat tu trung va sort theo ten vat tu
m = Sheets("THVT").Range("I65000").End(xlUp).Row
For I = m To 10 Step -1
Sheets("THVT").Cells(I, 9).Select
If Sheets("THVT").Cells(I, 9) > 1 Then
Selection.EntireRow.Delete
End If
Next
m = Sheets("THVT").Range("C65000").End(xlUp).Row
Range("B10:C" & m).Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Lay tong so luong vat tu va tien cua tung vat tu
N = Sheets("PTVT").Range("I65000").End(xlUp).Row
m = Sheets("THVT").Range("C65000").End(xlUp).Row
Columns("I:I").Select
Selection.ClearContents
For I = 10 To m
Sheets("THVT").Cells(I, 1) = I - 9 'So TT
Sheets("THVT").Cells(I, 4) = "=SUMIF('PTVT'!R7C4:R" & N & "C4,RC[-2],'PTVT'!R7C8:R" & N & "C8)"
Sheets("THVT").Cells(I, 4).Select
Selection.NumberFormat = "#,##0.000"
Sheets("THVT").Cells(I, 5) = "=VLOOKUP(RC[-3],'Vat_Lieu'!R4C3:R1500C6,3,0)"
Sheets("THVT").Cells(I, 5).Select
Selection.NumberFormat = "#,##0.000"
Sheets("THVT").Cells(I, 6) = "=VLOOKUP(RC[-4],'Vat_Lieu'!R4C3:R1500C6,4,0)"
Sheets("THVT").Cells(I, 6).Select
Selection.NumberFormat = "#,##0.000"
Sheets("THVT").Cells(I, 7) = "=ROUND(RC[-2]*RC[-3],0)"
Sheets("THVT").Cells(I, 7).Select
Selection.NumberFormat = "#,##0.000"
Sheets("THVT").Cells(I, 8) = "=ROUND(RC[-4]*RC[-2],0)"
Sheets("THVT").Cells(I, 8).Select
Selection.NumberFormat = "#,##0.000"
Next
'Trang tri bang va tính tong tien vat tu
m = Sheets("THVT").Range("F65000").End(xlUp).Row
Range("A10:H" & m).Select
With Selection
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With
Range("G" & m + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R10C7:R" & m & "C7)"
Selection.NumberFormat = "#,##0.000"
Range("H" & m + 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R10C8:R" & m & "C8)"
Selection.NumberFormat = "#,##0.000"
'ActiveWorkbook.Names.Add "CL_VLieu", "=SUM(R10C8:R" & m & "C8)-SUM(R10C7:R" & m & "C7)"
'Dat Name tinh Chenh lech vat lieu giua gia thong bao va dinh muc
Application.Names.Add Name:="CL_VLieu", RefersTo:="=SUM(R10C8:R" & m & "C8)-SUM(R10C7:R" & m & "C7)"
Range("A" & m + 1).Select
ActiveCell.FormulaR1C1 = "."
Range("B" & m + 1).Select
ActiveCell.FormulaR1C1 = "T" & ChrW(7892) & "NG C" & ChrW(7896) & "NG:"
Range("A" & m + 1 & ":H" & m + 1).Select
Selection.Font.FontStyle = "Bold"
With Selection
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With
Application.ScreenUpdating = True
'Dat vung in
ActiveSheet.PageSetup.PrintArea = "$A$" & 1 & ":$H$" & m + 5
End Sub
