NH_DK
Let's patience
- Tham gia
- 29/7/10
- Bài viết
- 865
- Được thích
- 1,203
- Nghề nghiệp
- Kế toán
Lớp mình tham khảo bài tập của mình ở mục này nhé!
Code:
Find đính kèm ne....
Code:
PHP:
Option Explicit
Sub Loc_Ctiet()
Application.ScreenUpdating = False
Dim ShSoCtiet As Worksheet
Dim ShSoData As Worksheet
Set ShSoCtiet = Sheets("SOCTIET")
Set ShSoData = Sheets("CSDL")
Dim eRw As Long, eRw1 As Long, i As Long, Kyhieu As String, Ma As String
'Xoa du lieu
ShSoCtiet.Range("A10:G56536").Clear 'Contents
eRw1 = 10
With ShSoCtiet
Kyhieu = Trim(.[c6])
Ma = Trim(.[c7])
For eRw = 4 To ShSoData.[a65536].End(xlUp).Row
'Dieu kien
If Trim(ShSoData.Cells(eRw, 1)) = Kyhieu And Trim(ShSoData.Cells(eRw, 4)) = Ma Then
'Lay du lieu sang neu dieu kien la dung
.Cells(eRw1, 1).Resize(, 2) = ShSoData.Cells(eRw, 2).Resize(, 2).Value
.Cells(eRw1, 3).Resize(, 6) = ShSoData.Cells(eRw, 5).Resize(, 6).Value
eRw1 = eRw1 + 1
End If
Next
End With
'Thong bao neu khong tim thay va thoat luon
If eRw1 = 10 Then MsgBox "Khong tim thay. Vui long tim lai nha!": Exit Sub
i = Range("A65535").End(xlUp).Row + 1
With [A10].Resize(i - 9, 7) 'Ke bang
.BorderAround LineStyle:=1
.Borders(11).LineStyle = 1: .Borders(11).ColorIndex = 7
.Borders(12).LineStyle = 1: .Borders(12).ColorIndex = 7
End With
With Cells(i, 3) 'Dien chu Cong vao bang
.Value = "Cong"
.Font.Bold = True
End With
With Cells(i, 7) 'Cong tong cot G
.Value = "=SUM(R10C7:R" & i - 1 & "C)"
.Font.Bold = True
End With
Range("E10:G" & i).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" ' Dinh dang so
Application.ScreenUpdating = True
Set ShSoCtiet = Nothing
Set ShSoData = Nothing
End Sub